Trying to insert/update two different tables with multiple values

Question

I'm trying to update two different tables on mySQL database with PHP code. One block works perfectly but from "if(isset..." I get the "Error querying database" message so it obvious the code where I'm trying to add the array values (checkbox) aren't working. What am I not seeing?

EDIT.PHP

   <h3>Edit Profile: <?php echo $_REQUEST['first_name'];?></h3>
   <form enctype="multipart/form-data" method="POST" action="change.php"> 
   <table border="0" width="60%">

   <tr><td width="30%">First Name: </td><td><input type="text"
   name="upd_first_name" value="<?php echo $_REQUEST['first_name'];?>" maxlength="20"> </td></tr>

   <tr><td width="30%">Last Name: </td><td><input type="text"
   name="upd_last_name" value="<?php echo $_REQUEST['last_name'];?>" maxlength="20"> </td></tr>

   <tr><td width="30%">Email: </td><td><input type="text"
   name="upd_email" value="<?php echo $_REQUEST['email'];?>" maxlength="45"> </td></tr>

   <tr><td width="30%">Password: </td><td><input type="password"
   name="upd_password" id="upd_password" value="<?php echo $_REQUEST['password'];?>" maxlength="20"> </td></tr>

   <tr><td width="30%">Confirm Password: </td><td><input type="password"
   name="upd_cpassword" id="upd_cpassword" value="<?php echo $_REQUEST['password'];?>" maxlength="20" onkeyup="checkPass(); return false;"> </td></tr>

   <tr><td width="30%">Profile Visbility: </td><td><input type="radio"    name="upd_profilevis" value="1" id="1" checked> Private <input type="radio"   name="upd_profilevis" value="2" id="2" > Public </td></tr>

  <<tr><td width="30%">Industries: </td><td>
  <input type="checkbox" name="industries[]" value="1"/>None</br>
  <input type="checkbox" name="industries[]" value="2"/>Film</br>
  <input type="checkbox" name="industries[]" value="3"/>Television</br>
  <input type="checkbox" name="industries[]" value="4"/>Music</br>
  <input type="checkbox" name="industries[]" value="5"/>Gaming</br>
  <input type="checkbox" name="industries[]" value="6"/>Books</br>
  <input type="checkbox" name="industries[]" value="7"/>Comic Books</br>
  </td></tr>

  <tr><td width="30%">Link: </td><td><input type="text"
  name="upd_link" value="<?php echo $_REQUEST['profile_link'];?>" maxlength="45"> </td></tr>

  <tr><td width="30%">Bio: </td><td><input type="text"
  name="upd_bio" value="<?php echo $_REQUEST['bio'];?>" maxlength="500"> </td></tr>

  <input type="hidden" name="MAX_FILE_SIZE" value="10000000">
  <tr><td width="30%">Picture: </td><td><input type="file" id="image" name="image"></tr>
  </table>
  <span id="confirmMessage" class="confirmMessage"></span><br /> 

  <input type="submit" value="Save & Update"/>
  <input type="hidden" name="id" value="<?php echo $_REQUEST['id'];?>">
  </form>

CHANGE.PHP (where I get the error)

<?php
//pulls data/files from the edit.php form
$id= $_REQUEST['id'];
$upd_first_name= $_REQUEST['upd_first_name'];
$upd_last_name= $_REQUEST['upd_last_name'];
$upd_email= $_REQUEST['upd_email'];
$upd_password= $_REQUEST['upd_password'];
$upd_cpassword= $_REQUEST['upd_cpassword'];
$upd_profilevis= $_REQUEST['upd_profilevis'];
$upd_link= $_REQUEST['upd_link'];
$upd_bio= $_REQUEST['upd_bio'];
$mypic = $_FILES['image']['name'];
$temp = $_FILES['image']['tmp_name'];
$type = $_FILES['image']['type'];


//checks if the picture is of the right type before inserting
if(($type=="image/jpeg") || ($type=="image/jpg") || ($type=="image/png") || ($type=="image/bmp") || ($type=="image/gif")) {

    //connects to the database
    $dbc = mysqli_connect('localhost', 'root', 'root', 'profile') or die('Error connecting to MySQL server.');

    //if industries are selected on the form edit.php insert into table 
    //each industry assigned to the member ID (junction table)
        if(isset($_POST['industries'])) {

            //deletes from table current values
            $query = "DELETE FROM `industry_has_member` WHERE member_idmember='$id'";
            $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));
            //to report errors
            if(!$result)
                {
                printf("Errormessage: %s\n", mysqli_error($dbc));
                }
            //adds on table new values
            foreach ($_POST['industries'] as $industry) {
            $query = "INSERT INTO industry_has_member (industry_idindustry, member_idmember) values ('$industry', '$id')";
            $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));
            //to report errors
            if(!$result)
                {
                printf("Errormessage: %s\n", mysqli_error($dbc));
                }
            }
        }

    $query = "UPDATE `member` SET member_stamp=now(), first_name='$upd_first_name', last_name='$upd_last_name', email='$upd_email', password='$upd_password',       profile_vis_idprofile_vis='$upd_profilevis', profile_link='$upd_link', prof_image='$mypic', bio='$upd_bio' WHERE idmember='$id'";

    //displays the results into a table
    $result = mysqli_query($dbc, $query) or die('Error querying database.');

    move_uploaded_file($temp,"images/$mypic");

    echo "<h2>Your profile has been updated</h2><br /><h3>Here is your profile picture</h3>";
    echo "<img border='1' width='200' height='200' src='images/$mypic'>";

    mysqli_close($dbc);

    } 
?>

can you replace `die('Error querying database')` with `die(mysqli_error($dbc))` to show the mysql error msg? — Fabricator, Jun 07 '14 at 18:16
So I replaced both die statements with the ones you suggested and it works...but only for new entries. If I had already the data in the database that's when it doesn't work and showed this... Duplicate entry '2-6' for key 'PRIMARY' so my problem is with updating existing entries with a new update…what would you suggest I add to the code to make sure both cases work? — Emmanuel Henri, Jun 08 '14 at 00:29
You have two other problem in your code : SQL Injection ( in "...VALUES ('$industries', '$id')...") and Cross-site scripting ( in '...value=" — Tom, Jun 08 '14 at 10:09
Please take a look at my comments at the last post below and let me know what you think. — Emmanuel Henri, Jun 10 '14 at 15:03
I think this post answered my question: http://stackoverflow.com/questions/19540781/selecting-or-deselecting-checkboxes-and-running-different-queries-in-php — Emmanuel Henri, Jun 10 '14 at 15:55
@EmmanuelHenri, yes. it basically removes the old records and inserts new ones for that industry — Fabricator, Jun 10 '14 at 17:36
After looking at my code and logic I finally found the answer to my logic and code and updated it (change.php) and it works!!! thanks y'all for your help it helped me figure it out. — Emmanuel Henri, Jun 10 '14 at 18:30

Fabricator · Answer 1 · 2014-06-10T17:58:56.267

1

You need to wrap all operations in a transaction.

if (isset($_POST['industries'])) {
    $industry_ids = $_POST['industries'];
    $id = $_POST['id'];

    try {
        $dbc->begin_transaction();
        $dbc->query("delete from industry_has_member where member_idmember=$id") or throw new Exception($dbc->error);

        foreach ($industry_ids as $industry_id) {
            $dbc->query("insert ignore into industry_has_member (industry_idindustry, member_idmember) values ($industry_id, $id)" or throw new Exception($dbc->error);
        }
        $dbc->commit();
    } catch (Exception $e) {
        echo $e->getMessage();
        $dbc->rollback();
    }
}

edited Jun 10 '14 at 17:58

answered Jun 08 '14 at 00:37

Fabricator

12,722
2
27
40

Please take a look at my comments at the last post below and let me know what you think. – Emmanuel Henri Jun 10 '14 at 15:03
@EmmanuelHenri, yes it makes sense now. I've updated the answer accordingly – Fabricator Jun 10 '14 at 17:17
I'm assuming this function goes under the if(isset) and foreach() functions? – Emmanuel Henri Jun 10 '14 at 17:33
thanks. Can you quickly look at my updated code above (change.php) and let me know if this is right? – Emmanuel Henri Jun 10 '14 at 17:43
@EmmanuelHenri, sorry I had misunderstood your data structure. see the updated answer – Fabricator Jun 10 '14 at 17:54
I'm actually seeing an error at the 2 query lines :( – Emmanuel Henri Jun 10 '14 at 17:55
After looking at my code I tried a new logic and it worked!!! Thanks y'all for all your help. It helped figure it out!!! I've edited my code (change.php) to reflect my changes. – Emmanuel Henri Jun 10 '14 at 18:26

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

Try to extend your insert question with

on duplicate key update

See: Insert into a MySQL table or update if exists

edited May 23 '17 at 10:26

Community

1
1

answered Jun 08 '14 at 00:38

user3622622

107
1
9

Use the above if you want to overwrite. Use user3678068's answer if you don't want to overwrite. – user3622622 Jun 08 '14 at 00:40
What I really want to is update. If a user for example has industry 2-3-4 already in the mysql db, and when the user updates it with the form he selects 1-4-6 instead I want only 1-4-6 to show in the db with this user. Right now with the REPLACE INTO or UPDATE ON DUPLICATE statements the DB would show 1-2-3-4-6 with this user ID. this I think I will need to approach this one differently. I'll need to create a different name when items are checked and when they unchecked so I create the logic to delete unchecked items and add checked items. What do you guys think? – Emmanuel Henri Jun 10 '14 at 15:01
After looking at my code and logic I finally found the answer to my logic and code and updated it (change.php) and it works!!! thanks y'all for your help it helped me figure it out. – Emmanuel Henri Jun 10 '14 at 18:30

Trying to insert/update two different tables with multiple values

2 Answers2