retro Posted May 2, 2008 Share Posted May 2, 2008 Sorry if the answer is glaringly obvious here, but I am very new to MySQL and am basically learning as I go! I have a MySQL4 database, which is standard MyISAM. In the table 'persons' I have fields ID, FirstName and LastName. In the table 'orders' I have ID, customer, date and several Item fields (Item1, Item2 etc.) I am using the following query: $DelDate = $_POST["DelDate"]; $result = mysql_query ("SELECT ID,customer,Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8,Item9,Item10,date FROM orders WHERE DAYOFWEEK(date)=DAYOFWEEK(' . $DelDate . ')"); $total = mysql_num_rows($result); I then use a while loop to echo the $row data into a table. This works fine. However, the data in 'customer' is a number, which matches 'ID' in 'persons'. I want to display the first and last name instead, by looking these up in 'persons'. My database knowledge comes from Access, so I know I'd do this with relationships. How would I go about creating relationships on my current database, though? I don't want to lose any data currently in the database. I've seen people mention foreign keys, but from what I gather you can't have foreign keys with MyISAM. How can I solve this? Any help would be greatly appreciated. Thanks in advance! Link to comment https://forums.phpfreaks.com/topic/103875-solved-linking-tables/ Share on other sites More sharing options...
947740 Posted May 2, 2008 Share Posted May 2, 2008 $query = "Select FirstName, LastName from persons where ID = '$customer'"; Link to comment https://forums.phpfreaks.com/topic/103875-solved-linking-tables/#findComment-531716 Share on other sites More sharing options...
wrongmove18 Posted May 2, 2008 Share Posted May 2, 2008 $Q = "SELECT * FROM `orders` LEFT JOIN `persons` ON (`persons`.`ID` = `orders`.`customer`) WHERE DAYOFWEEK(date)=DAYOFWEEK($DelDate)"; Link to comment https://forums.phpfreaks.com/topic/103875-solved-linking-tables/#findComment-531719 Share on other sites More sharing options...
retro Posted May 2, 2008 Author Share Posted May 2, 2008 Thanks! The LEFT JOIN makes perfect sense Link to comment https://forums.phpfreaks.com/topic/103875-solved-linking-tables/#findComment-531816 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.