Updateing tables in mysql *RESOLVED*

**Muk108** · Sep 18th, 2003, 07:43 PM

can i update more than one peice of information, i dont know the right word. what i have made is this page for the admin to see if the information submited buy users is factual or not. And i had a it print out the information from the database to see it then i had a checkbox to say if it was able to be viewed by the public. but im having some problems with it.. i dont understand how to make it update more than one or one, sql thing. ill show yo uhave i have....

PHP Code:


<form action="admin_check.php" method="post">

<?php

include("./admin/config.php");

mysql_connect($servername,$dbusername,$dbpassword) or die("Unable to connect to SQL server"); 

        mysql_select_db($dbname) or die("Unable to select database");



if (isset($_POST['a_accept'])) {



$sql = "SELECT * FROM alumni";

$alumni = mysql_query($sql) or die(mysql_error());

while ($alumnus = mysql_fetch_array($alumni)) {



    $id = $_POST[$alumnus['alumni_id']];

$update = "UPDATE `alumni` SET `alumni_ax` = '1' WHERE `alumni_id` = '$id'";

mysql_query($update);

}

} else {

        

$sql = "SELECT * FROM alumni";

$alumni = mysql_query($sql) or die(mysql_error());



echo '<table><tr><td colspan=3><hr color="#000000" noshade></td></tr>

    <tr>';



while ($alumnus = mysql_fetch_array($alumni)) {

    if ($alumnus['alumni_ax'] == 0) {

    if ($i == 3) {

        echo '</tr><tr>';

        $i = 0;

    }

    echo '<td width="227" align="left" valign="top"><table><tr><td><b>' . $alumnus['alumni_name'] . '</b></td></tr>';

    echo '<tr><td>' . $alumnus['alumni_year'] . '</td></tr>';

    echo '<tr><td>' . $alumnus['alumni_address'] . '</td></tr>';

    echo '<tr><td>' . $alumnus['alumni_phone'] . '</td></tr>';

    if ($alumnus['alumni_email'] == '')  { echo '<tr><td>n/a</td></tr>'; } else { echo '<tr><td><a href="mailto:' . $alumnus['alumni_email'] . '">' . $alumnus['alumni_email']. '</a></td></tr>'; }

    echo '<tr><td>' . $alumnus['alumni_afterschool'] . '</td></tr>';

    echo '<tr><td>' . '<input name="' . $alumnus['alumni_id'] . '" type="checkbox">' . ' Acceptable?</td></td></table><br></td>';



    $i++;

    }

}



echo '</tr>';



?>

<tr>

    <td colspan=3>

    <hr noshade color=black>

    Are You sure these submissions are ok?<br>

    <input name="a_accept" type="submit" value="Accept"> <input name="Clear" type="reset" value="clear">

    </td>

</tr>

<?

echo '</table><br>';

}

?>

</form>

i hope you understood what i was talking about, and can help. thanks

**The Hobo** · Sep 18th, 2003, 07:55 PM

I'm confused as to what you're doing, but if you're asking about updating more than one field in a record at a time, then yes you can. The syntax would be like:

Code:

UPDATE tblName SET field1='value1', field2='value2', field3='value3' WHERE id=1

Is that what you were asking?

**Muk108** · Sep 18th, 2003, 08:47 PM

im asking about updateing different id's but the feild i am updateding is the same, and it would be filled with the same number.

**kows** · Sep 18th, 2003, 10:26 PM

you could try

Code:

UPDATE tablename SET field='value' WHERE id='1' OR id='2'

and just keep adding OR's.. if there's lots of ID's, there's always while() or for() loops..

Code:

$query = "UPDATE tablename SET field='value' WHERE ";
$numberofid = 5;
for($i = 1; $i <= $numberofid; $i++){
  if($i > 1){
    $query .= "OR ";
  }
  $query .= "id='$i' ";
}
mysql_query($query);

it will produce something like

Code:

UPDATE tablename SET field='value' WHERE id='1' OR id='2' OR id='3' OR id='4' OR id='5'

**Muk108** · Sep 19th, 2003, 06:11 AM

ok but im not really sure how i could put this in the code to get it after i have clicked the submition button?

also how couldi keep each result displayed uniq(sp)?

**The Hobo** · Sep 19th, 2003, 11:12 AM

What you're going to want to do is make the checkboxes a set of arrays that hold the ID values. That way, when you get to the submission in your PHP script, you can loop through the array (it will only contain the ones that were checked), and build your SQL statement.

Here's an (untested) example. Beware of syntax errors and such:

Code:

<?php 
    include("./admin/config.php"); 
    mysql_connect($servername,$dbusername,$dbpassword) or die("Unable to connect to SQL server");  
    mysql_select_db($dbname) or die("Unable to select database"); 

    if (isset($_POST['a_accept'])) { 
        $sql = "SELECT * FROM alumni"; 
        $alumni = mysql_query($sql) or die(mysql_error()); 

        while ($alumnus = mysql_fetch_array($alumni)) {
            if (count($_POST['alumid']) != 0) {
                $sql = "UPDATE alumi SET alumni_ax='1' WHERE ";

                for ($i = 0; $i < count($_POST['alumid']); $i++) {
                    $sql .= " alumni_id='" . $_POST['alumid'][$i] . "'";

                    if ($i != (count($_POST['alumid']) - 1)) {
                        $sql .= " OR ";
                    }
                }
                
                mysql_query($sql);
            }
        } 
    } else { 
         
        $sql = "SELECT * FROM alumni"; 
        $alumni = mysql_query($sql) or die(mysql_error()); 

        echo '<form action="admin_check.php" method="post"> 
        <table>
            <tr>
                <td colspan=3><hr color="#000000" noshade></td>
            </tr> 
            <tr>'; 

        while ($alumnus = mysql_fetch_array($alumni)) { 
            if ($alumnus['alumni_ax'] == 0) { 
                if ($i == 3) { 
                    echo '</tr><tr>'; 
                    $i = 0; 
                } 

                echo '<td width="227" align="left" valign="top">
                    <table>
                        <tr>
                            <td><b>' . $alumnus['alumni_name'] . '</b></td>
                        </tr>
                        <tr>
                            <td>' . $alumnus['alumni_year'] . '</td>
                        </tr>'; 
                        <tr>
                            <td>' . $alumnus['alumni_address'] . '</td>
                        </tr>
                        <tr>
                            <td>' . $alumnus['alumni_phone'] . '</td>
                        </tr>'; 
                
                if ($alumnus['alumni_email'] == '') { 
                    echo '<tr><td>n/a</td></tr>'; 
                } else { 
                    echo '<tr>
                        <td><a href="mailto:' . $alumnus['alumni_email'] . '">' . $alumnus['alumni_email']. '</a></td>
                    </tr>'; 
                } 
                
                echo '<tr>
                    <td>' . $alumnus['alumni_afterschool'] . '</td>
                </tr>
                <tr>
                    <td><input name="alumid[]" value="' . $alumnus['alumni_id'] . '" type="checkbox">Acceptable?</td>
                </td>
                </table><br></td>'; 

                $i++; 
            } 
        } 

        echo '</tr>
            <tr> 
                <td colspan=3> 
                    <hr noshade color=black> 
                    Are You sure these submissions are ok?<br> 
                    <input name="a_accept" type="submit" value="Accept"> <input name="Clear" type="reset" value="clear"> 
                </td> 
            </tr> 
        </table><br></form>'
    }
?>

Important changes are in bold.

I also changed your SQL statements a bit. Having `quotes` around field names isn't usually required, and in this case, they aren't.

If your alumni_ax and alumni_id fields are INT fields, they you also do not need 'quotes' around them, either. But I left them there just in case.

**phpman** · Sep 19th, 2003, 05:59 PM

Originally posted by The Hobo
If your alumni_ax and alumni_id fields are INT fields, they you also do not need 'quotes' around them, either. But I left them there just in case.

not neccassarily. I have had instances of it failing if they didn't have single quotes. I always use them just for that reason. because just my luck they would fail when i didn't.

so it is up to you I guess, I was just stating that I have had it fail.

**The Hobo** · Sep 19th, 2003, 10:27 PM

Originally posted by phpman
not neccassarily. I have had instances of it failing if they didn't have single quotes. I always use them just for that reason. because just my luck they would fail when i didn't.

so it is up to you I guess, I was just stating that I have had it fail.

Well I never have.

**kows** · Sep 19th, 2003, 11:37 PM

Yeah, unless you do decimals or plain text when entering it, you don't need any quotes around it. I just add quotes because I'm usually entering in variables, and not just plain numbers, so I could end up with some decimals or strings in it.

hmm, now I'm wondering if you can just do something like:

Code:

$table = "table";
$var = "variable and stuff";
$query = "UPDATE $table SET var=$var WHERE id=1";
mysql_query($query);

and would it auto-quote $var, and actually print it to MySQL like:

Code:

UPDATE table SET var="variable and stuff" WHERE id=1

I think I'll go try that out, but probably not tonight because I'm busy.

**Muk108** · Sep 20th, 2003, 11:22 PM

yeah i tryed that and it still doesnt update...and there is a problem it was just missing a few semicolins but i got them. any other ideas as to why its not working?

**phpman** · Sep 20th, 2003, 11:42 PM

try this and tell us what you get

PHP Code:


<?php 

    include("./admin/config.php"); 

    mysql_connect($servername,$dbusername,$dbpassword) or die("Unable to connect to SQL server");  

    mysql_select_db($dbname) or die("Unable to select database"); 



    if (isset($_POST['a_accept'])) { 

        $sql = "SELECT * FROM alumni"; 

        $alumni = mysql_query($sql) or die(mysql_error()); 



        while ($alumnus = mysql_fetch_array($alumni)) {

            if (count($_POST['alumid']) != 0) {

                $sql = "UPDATE alumni SET alumni_ax='1' WHERE ";



                for ($i = 0; $i < count($_POST['alumid']); $i++) {

                    $sql .= " alumni_id='" . $_POST['alumid'][$i] . "'";



                    if ($i != (count($_POST['alumid']) - 1)) {

                        $sql .= " OR ";

                    }

                }

                

               $result= mysql_query($sql);

               if(!$result){echo mysql_error(); }

            }

        } 

    } else { 

         

        $sql = "SELECT * FROM alumni"; 

        $alumni = mysql_query($sql) or die(mysql_error()); 



        echo '<form action="admin_check.php" method="post"> 

        <table>

            <tr>

                <td colspan=3><hr color="#000000" noshade></td>

            </tr> 

            <tr>'; 



        while ($alumnus = mysql_fetch_array($alumni)) { 

            if ($alumnus['alumni_ax'] == 0) { 

                if ($i == 3) { 

                    echo '</tr><tr>'; 

                    $i = 0; 

                } 



                echo '<td width="227" align="left" valign="top">

                    <table>

                        <tr>

                            <td><b>' . $alumnus['alumni_name'] . '</b></td>

                        </tr>

                        <tr>

                            <td>' . $alumnus['alumni_year'] . '</td>

                        </tr>'; 

                     echo'   <tr>

                            <td>' . $alumnus['alumni_address'] . '</td>

                        </tr>

                        <tr>

                            <td>' . $alumnus['alumni_phone'] . '</td>

                        </tr>'; 

                

                if ($alumnus['alumni_email'] == '') { 

                    echo '<tr><td>n/a</td></tr>'; 

                } else { 

                    echo '<tr>

                        <td><a href="mailto:' . $alumnus['alumni_email'] . '">' . $alumnus['alumni_email']. '</a></td>

                    </tr>'; 

                } 

                

                echo '<tr>

                    <td>' . $alumnus['alumni_afterschool'] . '</td>

                </tr>

                <tr>

                    <td><input name="alumid[]" value="' . $alumnus['alumni_id'] . '" type="checkbox">Acceptable?</td>

                </td>

                </table><br></td>'; 



                $i++; 

            } 

        } 



        echo '</tr>

            <tr> 

                <td colspan=3> 

                    <hr noshade color=black> 

                    Are You sure these submissions are ok?<br> 

                    <input name="a_accept" type="submit" value="Accept"> <input name="Clear" type="reset" value="clear"> 

                </td> 

            </tr> 

        </table><br></form>';

    }

?>

**Muk108** · Sep 21st, 2003, 12:13 AM

it had the same two errors and a few more, in the first few lines the database thing was the wrong table, changed that and then

PHP Code:


                            <td><b>' . $alumnus['alumni_name'] . '</b></td> 

                        </tr> 

                        <tr> 

                            <td>' . $alumnus['alumni_year'] . '</td> 

                        </tr>';

shouldnt have the ' or ; there

then you need a ;

PHP Code:


               </td>  

            </tr>  

        </table><br></form>'

and still no luck

**phpman** · Sep 21st, 2003, 10:02 AM

after you fixed those errors what did it tell you? did it echo a error message?

I just copied the code from Hobo and added something, didn't check for errors.

I fixed my code so try it again

**Muk108** · Sep 21st, 2003, 09:11 PM

ok its working now thank you very much!

Thread: Updateing tables in mysql RESOLVED

Thread Tools

Display