genista Posted October 31, 2006 Share Posted October 31, 2006 Hi,I am slightly lost on this one. I have one table for suppliers, and at the moment it is storing everything - so the time has come to clean things up and add new tables. SO now I have a new table called supplierimages, the only thing it shares with suppliers is the supplierid. Now what I need to know is how to modify the code below to insert the supplierid into the supplierimages table if NULL and then from there to update the image1 field into supplierimages rather than suppliers. As you can see the first query is retrieving data from suppliers where there is a match with the sessionid (sessionid is the username, sessionid and pasword from the supliers table).Script is work in progress so please ignore any other issues..[code=php:0] <?php session_start();include_once ("../../private/supplierconfig.php"); checkLoggedIn("yes");error_reporting (E_ALL); //Retrieve details from database to start $id = $_SESSION['username'];echo $id;$query = "select username, supplierid, image1 from suppliers where username='$id'"; $result=mysql_query($query, $link) or die("MySQL query $query failed. Error if any: ".mysql_error());echo $query; //get the first (and only) row from the result $row = mysql_fetch_array($result, MYSQL_ASSOC); $username=$row['username']; $image1 = $row['image1']; if(isset( $submit )) { //If the Submitbutton was pressed do: if ($_FILES['imagefile']['type'] == "image/jpeg"){ $_FILES['imagefile']['name'] = basename($_FILES['imagefile']['name']);copy ($_FILES['imagefile']['tmp_name'], "files/".$username.$_FILES['imagefile']['name']) or die ("Could not copy"); echo ""; echo "Name: ".$_FILES['imagefile']['name'].""; echo "Size: ".$_FILES['imagefile']['size'].""; echo "Type: ".$_FILES['imagefile']['type'].""; echo "Upload Complete...."; $image1 = $username.$_FILES['imagefile'] ['name']; echo "$image1"; $query = "UPDATE `supplierimages` SET `image1` = '$image1', `username` = '$username' WHERE `username` = '". mysql_real_escape_string($_SESSION['username']). "' LIMIT 1"; $result = mysql_query($query, $link) or die('Update failed: ' . mysql_error()); echo $query; //print_r($query); mysql_info($link) ; if(mysql_affected_rows($link) == 0); } } else { echo "<br><br>"; } /*if( ( $image1 == 'NULL' ) || ($image1 =='' ) ) { // header("Location: uploadimage.php"); echo "Please use the browse button below to locate an image file from your computer, press submit to upload it. This site accepts jpeg images only."; } else { // echo "<meta http-equiv='refresh' content='0;url=uploadimage.php'>"; }*/?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN "http://www.w3.org/TR/html4/strict.dtd"> <html lang="en"> <form name="form1" method="post" action="#" enctype="multipart/form-data"> <input type=text name="username" value="<?php echo $username; ?>"> <input type="file" name="imagefile"> <p> </td><td><input name="submit" type="submit" value="Submit"></p> </table> </form>[/code]Thanks Quote Link to comment Share on other sites More sharing options...
btherl Posted November 1, 2006 Share Posted November 1, 2006 Since you're using supplierid as the identifier, it'll make life easier if you keep that value in a variable and use it for your database queries.I would suggest making a function which first tries to update the supplierimage. If that update affects 0 rows, then do an insert. That means you need only two queries.[code]UPDATE supplierimage SET image1='$image' WHERE supplierid = $supplierid-- Or, you can doUPDATE supplierimage SET image1 = '$image' WHERE supplierid = (SELECT supplierid FROM suppliers WHERE username = '$username')[/code]Notice how the queries are simpler if you know the supplierid already.Then, if mysql_affected_rows($result) == 0 for the update, do this:[code]INSERT INTO supplierimage (supplierid, image1)VALUES ($supplierid, '$image')[/code]Or if you only have username available, replace $supplierid with the subquery above:[code]INSERT INTO supplierimage (supplierid, image1)VALUES ((SELECT supplierid FROM suppliers WHERE username = '$username'), '$image')[/code]To fetch data[code]SELECT * FROM suppliers JOIN supplierimage USING (supplierid)[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.