kickstart

Hi You could declare variables in the table field and use eval to execute it, but this is a bit risky as someone could insert some fairly damaging code in there. Think echo $username='MrCool'; would produce 'McCool' irrespective of whether it is assigned a value first. I would be very tempted to split the variables off into a separate table:- Id MasterId FieldName FieldValue 1 1 username MrCool 2 1 superpower Awesomeness Then when your record from the main table is read you then read all the matching records (ie, MasterId) from this table. <?php $MasterId = 1; $somevars = mysql_query("SELECT FieldName, FieldValue FROM VarsTable WHERE MasterId = $MasterId"); while($row = mysql_fetch_array($somevars)) { $$row['FieldName'] = $row['FieldValue']; } ?> That said I wouldn't want to use variable variable names unless 100% necessary. Will likely give code that is very difficult to maintain and shouldn't be necessary. All te best Keith

Hi Being fair I hadn't given it an alias id in the earlier select I pasted. Put an alias of FriendId in there to make it more obvious. All the best Keith

Hi Think that should work. You can try forcing it by putting brackets around each SELECT statement, but it shouldn't make a difference:- $checkUpload_sql = "(SELECT id, artist, song, nothing, date, count FROM upload WHERE artist = '$follow') UNION (SELECT null, username, null2, bulletin, date, null3 FROM bulletin WHERE username = '$follow') ORDER BY date DESC"; However one thing that is possible would be if the date column is not a date type field. If it is just a char in dd/mm/ccyy format then the order would be as a character field rather than a date. All the best Keith

Hi Something like this <?php $mutual_friends = mysql_query("SELECT z.aFriend AS FriendId FROM (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) z INNER JOIN (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) x ON z.aFriend = x.aFriend "); while($mutual_check = mysql_fetch_array($mutual_friends)) { $mutual_photo = photo($mutual_check['FriendId'], 55); $mutual_name = name($mutual_check['FriendId'], 'fml'); ?> <li id="<?php print $mutual_id?>"> <img onClick="location.hash='#/profile&id=<?php print $mutual_check['FriendId'];?>'" class="friends-thumb" src="<?php print $mutual_photo?>" /><div class="friends-name" id="hover<?php print $mutual_check['FriendId'];?>"><div class="friend-arrow-border"><div class="friend-arrow"></div></div><a href="#/profile&id=<?php print $mutual_check['FriendId'];?>"><?php print $mutual_name?></a></div> </li> <?php } ?> No need to figure out which column it comes from as the SQL sorts that out. All the best Keith All the best Keith

Hi Worked when I had a play so maybe something to do with field types if you are using character id fields. It only brings back one column which should contain all the mutual friends irrespective of which column in the table they are in. $mutual_friends = mysql_query("SELECT z.aFriend FROM (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) z INNER JOIN (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) x ON z.aFriend = x.aFriend "); All the best Keith

Hi Easier than that. An INNER JOIN will find a row where there is a matching row on both tables. So in your example there is a record on Topics but as the replied id is empty there is no matching record on the users table. Hence no record is returned. Another type of join is an OUTER JOIN. This will return a record from one table whether there is a matching record on the other table or not. If there is a matching record then the columns from that table will have the appropriate values, but if not they will just be NULL. There are a few different types of OUTER JOIN, but 99% of the time you will use a LEFT OUTER JOIN, which will return the record from the table on the left whether or not there is a record on the table on the right. In the SQL I posted earlier change INNER JOIN to LEFT OUTER JOIN and it should do what you want. All the best Keith

Hi You can alias table names. SELECT * FROM Topics INNER JOIN Users Users1 ON Topics.owner_id = Users1.UserId INNER JOIN Users Users2 ON Topics.Replied = Users2.UserId In this case the Users table in joined twice, but referred to as Users1 or Users2 depending on which one you are referencing. All the best Keith

Hi Issue is the "USING BTREE" when using that on a mysql 5.0 database. Remove that (think it is the default for innodb anyway) and it will likely be fine. All the best Keith

Hi Another idea, but I think I might be over complicating things. This is assuming $id is one persons id and $id2 is the other persons id. SELECT z.aFriend FROM (SELECT if(a.friend_1 = $id,a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = $id AND a.friend_2 != $id2) OR (a.friend_2 = $id AND a.friend_1 != $id2)) z INNER JOIN (SELECT if(a.friend_1 = $id,a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = $id AND a.friend_2 != $id2) OR (a.friend_2 = $id AND a.friend_1 != $id2)) x ON z.aFriend = x.aFriend All the best Keith

Hi Tend to agree with the above. However I would think that you could left outer join friends with itself, where friend id on one is user id on the other AND vice versa. Then only return rows where there is no matching row. Something like this:- SELECT * FROM friends a LEFT OUTER JOIN friends b ON a.user_id = b.friend_id AND a.friend_id = b.user_id WHERE b.user_id IS NULL All the best Keith

Hi What do you mean "row for 11"? Is mid the key to another table and is 11 a value on that table? All the best Keith

Hi Yes. Something like this $query = mysql_query("SELECT manufacturers.name FROM manufacturers RIGHT JOIN products ON manufacturers.id = products.manufacturer"); While ( $row = mysql_fetch_array($query)) { switch (true) { case $row['brand'] == 'fred' : //some code for one brand break; case $row['brand'] == 'burt' : //some code for another brand break; } } All the best Keith

Hi You can JOIN 2 tables together in an UPDATE and update the values of columns in both tables. All the best Keith

Hi Yep, 2 tables. You would be best to use a JOIN. Something like:- SELECT a.user_id, a.username, a.password, a.email, a.first_name, a.last_name, a.gender, a.about_user, a.birthday, a.city, a.country, a.picture, b.bike_id, b.bike_type, b.frame, b.description, b.large_1, b.large_2, b.large_3, b.thumbnail FROM users a INNER JOIN cycles b ON a.user_id = b.user_id All the best Keith

Hi Best practice might well be to split them off. However not sure I would bother depending on the ratio of users to managers. All the best Keith

Hi I would say how you have it now is the best way to do it. It is best to avoid duplicating tables. All the best Keith

Hi You can't really have a different id as that id refers to the table record. What you can do is the 2nd suggestion I made. Doing a UNION and having an extra column that specifies the table that row came from. If you want a unique row number just for display purposes then you can use counter variables. Something like this:- SET @rownum = 0; SELECT @rownum:=@rownum+1,'State1', Id, Town FROM state1 UNION SELECT @rownum:=@rownum+1,'State2', Id, Town FROM state2 However to reference the original rows (such as to do any updates) you will still need the original Id and the table it is from. All the best Keith

Hi Have an extra column which is the version number of the row. When you want to update a record add one to the version number(s) of the matching records and insert the new one as version zero. All the best Keith

Hi I am not quite sure what you mean. If you are doing a join between several tables with ID fields then normally the tables would be different. Each would have their own ID but you would bring back the IDs of each table / record. And give each an alias so you can access them. If you mean that you have several databases with the same kind of data (ie, maybe you have a table then an archived table of old records) then you would probably UNION the results together. To tell which row comes from which table you have an extra column with a fixed value. For example SELECT 'MainTable' AS TableName, Id, CusName FROM MainTable UNION SELECT 'ArchiveTable' AS TableName, Id, CusName FROM ArchiveTable All the best Keith

Hi I changed the SQL a touch to do a SELECT * (not recommended for production use) to get all the columns and I can't see any duplicates. If I remove the WHERE clause for the session_id I can get a duplicate, but otherwise it seems fine. All the best Keith

Hi Can have a play, but to save me a load of typing could you export the table declarations and a small sample of data that can demonstrate the problem. However to me it looks like you just have several results for an assignment. Which I presume is correct as I presume an assignment has several questions each of which has a result. All the best Keith

Hi Using an example. Say you had a users details on one table. Each use can have 0 or more bikes, and another table lists those bikes. The bike table would probably have an autoincremented primary key, but would also have a column which contains the id of the owning user (which would probably be the autoincremented primary key of the user from the users table). When adding a user you would get the key of that user and then add the bikes with the key value from the users table being known. If you had bikes that could be associated with multiple users then you would use an intermediate table to link the users and bikes together. This would have its own primary key (which we probably wouldn't care in the slightest about its value) and then a column for the user and a column for the bike. Any user would have as many rows as required on this table for all the bikes they were associated with. Using your tables you would insert something like this:- $sql = "INSERT INTO `users` (`user_id`,`username`,`password`,`email`,`first_name`,`last_name`, `gender`,`about_user`,`birthday`,`city`,`country`,`picture`) VALUES (NULL,'Joe90','1234','[email protected]','Joe','Bloggs', 'm','Some random user','1990:12:25','London','UK','someimageurl'); $rs = mysql_query($sql) or die(mysql_error()." $sql"); $UserId = mysql_insert_id(); $sql = "INSERT INTO `cycles` (`bike_id`,`user_id`,`bike_type`,`frame`,`description`,`large_1`,`large_2`,`large_3`,`thumbnail`) VALUES(NULL,$UserId,'Big Bike','Big Frame','Some description of a bike','Some Text','Some Text','Some Text','imageurl'); $rs = mysql_query($sql) or die(mysql_error()." $sql"); All the best Keith

Hi No. Mysql knows nothing about the php sessions. If you want that you will have to manually do it. When you insert the user the user_id would likely be an integer autonumber field. You would use mysql_insert_id() to retrieve it and use that in child records you inserted afterwards. All the best Keith

Hi Normally you would insert the other records manually. If there is a 1 to 1 relationship then maybe putting it onto a separate table isn't required, while with a 1 to many relationship (presume a person can have many bikes) then it doesn't seem to make sense to randomly insert one bike (when they may not actually have any). All the best Keith

Hi Not really. You could set up a cron job to run every few minutes, but in this case not sure it is any better. A page displaying out of date info will be confusing however you do it. Updating the database automatically somehow will not fix the out of date page that is being looked at. Doing an update to the database when things change is effectively no different to doing the update when anyone views the data (ie, just prior to getting the data to display). If you want the data on display to reflect what is correct then you need to have the display update itself. Either using Javascript to calculate updates and update the display (ie, a count down). Or use Ajax to regularly query the database again and update the display. Personally just do the update on EVERY page refresh, whether it is to do with that auction or not. Done this way (and with decent keying) there shouldn't be many updates per page and they shouldn't take long. All the best Keith