I got my PHP/MYSQL start about a year ago with K. Yank book "Build Database Driven Website", so I am still new at coding PHP/MYSQL. My current project is building a real estate website - almost done. The CMS has form pages that ADD / EDIT / DELETE residential records. The ADD page creates a new residential record in the database; and the EDIT and DELETE pages manage each residential record. Each residential record has four pictures associated with that record. Adding pictures is done in the EDIT page. Under my design, the admin must first create a record using the ADD page. --------ISSUE--------- The ADD page inserts a record into the RESIDENTIAL TABLE; RES_ID is the primary key. I also need the ADD page to INSERT the newly created RES_ID into the IMAGES TABLE field IMAGE_RES_ID. <?php if ($submit): // add new CONTENT from form below $sqlinsert = "INSERT INTO residential SET res_order='$resorder', res_head='$reshead', res_description='$resdescription', res_price='$resprice' if (@mysql_query($sqlinsert)) { echo("<p>New Residential property added!</p>"); } else { echo("<p>Error adding new Residential property: " . mysql_error() . "</p>"); } ?> PHP: My research and thoughts make this code (or something like it) look like a solution but my testing proves me wrong on its placement in the code and it syntax. $id=mysql_insert_id(); $imginsert = "INSERT INTO image SET res_order='$id'"; Any one see where I went off the road? Thanks in advance. Greg
Funny thing, I have seen that syntax before while researching this issue. It was the first time I had seen it, and the field names were indise of `` tic marks. It looked like a solution, but my efforts to use it failed. The K. Yank book I used (last year) to learn PHP/MYSQL is a first edition Copyright 2001; so I think my syntax is outdated. I have already gotten some critics on Register Globals ON as opposed to OFF and I read up on the subject. I can see where I need to revamp my coding in the near future. In that regard, my IF statement is now: if (isset($_POST['submit'])): // add new CONTENT from form below
okay, found the solution [for this coding style]. I inserted this code before the end of the closing PHP tag ?> and I got the newly created ID primary key from the RESIDENTIAL table to be INSERTED into the IMAGE table in the image_res_id field. $id = mysql_insert_id(); $imginsert = "INSERT IGNORE INTO image SET image_res_id=$id"; if (@mysql_query($imginsert)) { echo("<p>New Residential ID added to Image table!</p>"); } else { echo("<p>Error adding Residential ID into Image table! " . mysql_error() . "</p>"); } ?> PHP:
While doing my research on the issue of inserting data into more than one table with the same form, I came across another solution that was written in a different syntax of code. I was wondering why it differs from the syntax I use. My FORM syntax: <?php if (isset($_POST['submit'])): // add new CONTENT from form below $sqlinsert = "INSERT INTO residential SET res_order='$resorder', res_head='$reshead', res_description='$resdescription', res_price='$resprice' if (@mysql_query($sqlinsert)) { echo("<p>New Residential property added!</p>"); } else { echo("<p>Error adding new Residential property: " . mysql_error() . "</p>"); } $id = mysql_insert_id(); $imginsert = "INSERT IGNORE INTO image SET image_res_id=$id"; if (@mysql_query($imginsert)) { echo("<p>New Residential ID added to Image table!</p>"); } else { echo("<p>Error adding Residential ID into Image table! " . mysql_error() . "</p>"); } ?> PHP: and other FORM syntax: <?php if(isset($_POST['artists'])): $artists = $_POST['artists']; $bio = $_POST['bio']; $gigdate = $_POST['gigdate']; $gigtime = $_POST['gigtime']; $price = $_POST['price']; $venue_name = $_POST['venue_name']; $venue_city = $_POST['venue_city']; $venue_address = $_POST['venue_address']; $venue_postcode = $_POST['venue_postcode']; $venue_tel = $_POST['venue_tel']; $sql = mysql_query("INSERT INTO `artists` ( `artists` , `bio` ) VALUES ('$artists', '$bio')"); $sql2 = mysql_query("INSERT INTO `listings` ( `gigdate` , `gigtime` , `price` ) VALUES ('$gigdate', '$gigtime', '$price')"); $sql3 = mysql_query("INSERT INTO `venue` ( `venue_name`, `venue_city`, `venue_address`, `venue_postcode`, `venue_tel` ) VALUES ('$venue_name', '$venue_city', '$venue_address', '$venue_postcode', '$venue_tel')"); if(@mysql_query($sql and $sql2 and $sql3)) { echo '<p>New Listing!</p>'; } else { echo '<p>Error:' . mysql_error() . '</p>'; } ?> PHP:
I've never seen mysql_query($sql and $sql2 and $sql3) syntax used before, is that even valid? 'and' is a logical operator, to me it seems like its a mysql_query(true) command being executed, weird if thats an actual way to query since how would you get the last insert id of multiple queries. Then again, I've never seen SET used in INSERT queries so anything is possible
My research took me to a couple of boards looking for a solution that would allow updating of multiple tables and I ran across that solution. I found the syntax puzzling (newbie) since I had not seen it before. The syntax looked like the syntax you suggested in our earlier post... So that is why I wonder what the difference was, my code is expressed differently. The bottom code (mataching your quoted syntax); it looks cleaner and more logical. The mysql_insert_id() is a function that reads back the last auto_incremented field number. My research gave a lot of cautions, but mainly, use it right after the auto increment field is updated so the current ID is reported. As in my case, the ADD page added a residnetial record to the RESIDENTIAL table and also wrote the ID for that record in the IMAGE table. I have updated the my post (12:33pm) above to show my complete solution.
I just did a test and mysql_query($sql and $sql2 and $sql3) does not work, not surprised since it doesn't make sense. If you want to run multiple queries with one call look into mysqli, stored procedures might also be useful but it's a little more complicated. INSERT INTO myTable (fieldA, fieldB) VALUES (1, 2); Is the "standard" way of doing inserts, I didn't even realize that you could do the SET stuff until I checked the docs after looking at your code. I would stick to the common way of doing things
My original task was to insert a new ID into another TABLE using the same form. (ADD new record and INSERT new ID into seperate table). And while researching this issue, I ran across this thread: http://www.sitepoint.com/forums/showthread.php?t=463592 The syntax was so different from what I had been writing; so I was wondering how out of date my coding syntax is? <?php if(isset($_POST['artists'])): $artists = $_POST['artists']; $bio = $_POST['bio']; $sql = mysql_query("INSERT INTO `artists` ( `artists` , `bio` ) VALUES ('$artists', '$bio')"); if(@mysql_query($sql)) { echo '<p>New Listing!</p>'; } else { echo '<p>Error:' . mysql_error() . '</p>'; } ?> PHP: I would write the same form as: <?php if (isset($_POST['submit'])): $sqlinsert = "INSERT INTO artist SET artists='$artists', bio='$bio' if (@mysql_query($sqlinsert)) { echo("<p>New Listing!</p>"); } else { echo("<p>Error" . mysql_error() . "</p>"); } ?> PHP: The two differences I see are in the syntax of the mysql_query line. The VARIABLES and the FIELD names are expressed differently, and the FIELD names have ` (accent marks) as opposed to ' (single quotes). This sytle makes me wonder about my syntax - since the K. Yank book I learned from was a 2001 first edition. And I have received some advice regarding REGISTERED GLOBALS and learning to code with them OFF.