Need help for updating multiple looping rows of SQL by php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Bahalul
Forum Newbie
Posts: 1
Joined: Sat Dec 31, 2011 5:35 am

Need help for updating multiple looping rows of SQL by php

Post by Bahalul »

Hi there,
I am getting stack of updating multiple looping rows of SQL by php. Can anyone help me, please? My code is showing result but it doesn't upload any query. Here I am writing my code

Code: Select all

<?php include('include/db_connect.php'); ?>
<?php  if(isset($_POST['Level'])){$Level = $_POST['Level'];
$id = "('" . implode( "','", $Level ) . "');" ;
$sql="UPDATE Webaccounts1 SET Level = $Level WHERE ID IN $id" ;
$result = mysql_query($sql) or die(mysql_error());
}
 
$sql="SELECT * FROM Webaccounts1";
$result=mysql_query($sql);

$count=mysql_num_rows($result);
?>
<form id="form1" name="form1" method="post" action=""> 
<table width="600" border="2">
<tr> <td colspan="4" ><input name="Update" type="submit" value="update" /> </td>  </tr>
<tr> <td width="98">Login Name</td><td width="177" >Rank </td> <td width="60">Class</td> <td width="170">Main Character Profile</td> </tr>
<?php
$result = mysql_query("SELECT * FROM Webaccounts1 Order By Charname");

//declaring value: 
       $counter="0"; 
	   
//Looping: 
	   while($row = mysql_fetch_array($result))  
	   {
//Applying condition		   
		   echo "<tr><td>".$row['Charname']."</td>"; $level=$row['level']; { ?>
		   
		   <td> <select name="Level[]">
				   <option value="<?php echo $row['Level'] ?>" selected="selected"><?php echo $row['Level']?></option>
				   <option value="Guest" >Guest</option>
				   <option value="Hall of Shame">Hall of Shame</option>
				   <option value="Initiate">Initiate</option>
				   <option value="Member">Member</option>
				   <option value="Veteran">Veteran</option>
				   <option value="Cadet">Cadet</option>
				   <option value="Event Manager">Event Manager</option>
				   <option value="Raid Leader">Raid Leader</option>
				   <option value="Battle Master and Bank Master">Battle Master and Bank Master</option>
				   <option value="Officer">Officer</option>
				   <option value="Legendary">Legendary</option>
				   <option value="Guild Master">Guild Master</option></select></td>

		   <td><?php echo $row['Class'] ?></td>
		   
		  <td><a href="http://armory.neverendless-wow.com/characters/<?php echo $row['ingame_ch'] ?>" target="_new"><?php echo $row['ingame_ch'] ?> </a></td>
		   	
		   </tr><?php }

		 $counter++;
	   } 
echo "</table>";
echo "counter = <input type=\"text\" name=\"total_items\" size=\"4\" value=\"" . $counter . "\">";
mysql_close($conect);?> 
<tr> <td colspan="4" ><input name="Update" type="submit" value="update" /> </td>  </tr>
    </form>
I guess there has problem of line

Code: Select all

$sql="UPDATE Webaccounts1 SET Level = $Level WHERE ID IN $id" ;
as i am getting message
Unknown column 'Array' in 'field list'
Please, Help me.

Kind Regards,
Bahalul
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Need help for updating multiple looping rows of SQL by p

Post by twinedev »

First rule when you have a failing SQL statement. echo out the actual statement being sent to the database, if you don't see anything wrong with it, then manually execute it on the DB.

You would see that your query looks like this, assuming $Level is set to array(3,6,8):
UPDATE Webaccounts1 SET Level = Array WHERE ID IN ('3','6','8')

I think you used the wrong variable ($Level) where you do Level = $Level as in the line above that it is clearly used as an array.

-Greg
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Need help for updating multiple looping rows of SQL by p

Post by twinedev »

Had more time to look through all the code and come up with something for you. Try the following:

Code: Select all

<?php

    require_once('include/db_connect.php');

    // Set up Levels here, so it is in one place, easier to update the list later.
    $aryLevels = array('Guest','Hall of Shame','Initiate','Member','Veteran',
                       'Cadet','Event Manager','Raid Leader','Battle Master and Bank Master',
                       'Officer','Legendary','Guild Master');

    // BEGIN: Update data
        if (count($_POST)>0) {
            $intUpCount = 0;
            foreach($_POST as $key=>$val) {
                // Make sure this is SELECT input
                if (preg_match('/^Level_([0-9]+)$/',$key,$regs)) {
                    $intID = $regs[1]; 
                    // Now, has it changed from what it originally was (saves on DB writes)
                    if ($strLevel != $_POST['Orig_'.$intID]) {
                        $SQL = 'UPDATE `Webaccounts` SET `Level`="'.mysql_real_escape_string($val).'" WHERE `ID`='.$intID;
                        mysql_query($SQL) or die($mysql_error());
                        $intUpCount++;
                    }
                }
            }
        }
    // END: Update data
    
    // BEGIN: Load up data                      
        $aryAccounts = array();
        $SQL = 'SELECT * FROM `Webaccounts1`';
        $rsAccounts = mysql_query($SQL) or die (mysql_error());
        if ($rsAccounts && mysql_num_rows($rsAccounts)>0) {
            while ($aryTemp = mysql_fetch_assoc($rsAccounts)) {
                $intID = $aryTemp['ID']; unset($aryTemp['ID']);
                $aryAccounts[$intID] = $aryTemp;
            }
            mysql_free_result($rsAccounts);
        }
        unset ($rsAccounts);
    // END: Load up data

   
    function echoHSC($strText) {
        echo htmlspecialchars($strText,ENT_QUOTES);
    }

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <meta http-equiv="Lang" content="en" />
    <title>My Page</title>
</head>
<body>
    <?php if(isset($intUpCount)): ?>
        <p>Updated <?php echo $intUpCount; ?> record(s).</p>
    <?php endif; ?>
    <form id="form1" name="form1" method="post" action="">
        <table width="600" border="2">
            <tr>
                <td colspan="4"><input name="Update" type="submit" value="update" /></td>
            </tr>
            <tr>
                <td width="98">Login Name</td>
                <td width="177">Rank</td>
                <td width="60">Class</td>
                <td width="170">Main Character Profile</td>
            </tr>
            <?php foreach($aryAccounts as $intID=>$aryAcct): ?>
                <tr>
                    <td><?php echoHSC($aryAcct['Charname']); ?></td>
                    <td>
                        <select name="Level_<?php echoHSC($intID); ?>">
                            <?php foreach($aryLevels as $strLevel): ?>
                                <?php if($aryAcct['Level']==$strLevel): ?>
                                    <option value="<?php echoHSC($strLevel); ?>" selected="selected"><?php echoHSC($strLevel); ?></option>
                                <?php else: ?>
                                    <option value="<?php echoHSC($strLevel); ?>"><?php echoHSC($strLevel); ?></option>
                                <?php endif; ?>
                            <?php endforeach; ?>
                        </select>
                        <input type="hidden" name="Orig_<?php echoHSC($intID); ?>" value="<?php echoHSC($aryAcct['Level']); ?>" />
                    </td>
                    <td><?php echo $row['Class'] ?></td>
                    <td><a href="http://armory.neverendless-wow.com/characters/<?php echo urlencode($aryAcct['ingame_ch']); ?>" target="_new"><?php echoHSC($aryAcct['ingame_ch']); ?> </a></td>
                </tr>
            <?php endforeach; ?>

            <!-- Guessed on formatting here, as original had an echo outside of <table> followed by a table row -->
            <tr>
                <td colspan="4">
                    Counter = <input type="text" name="total_items" size="4" value="<?php echo count($aryAccounts); ?>" />
                </td>
            </tr>
            <tr>
                <td colspan="4"><input name="Update" type="submit" value="update" /></td>
            </tr>
        </table>
    </form>
</body>
</html>
Post Reply