PHP - Updating Multiple Rows In Mysql Using Php

Hi Forum

I have this script below which i thought would update multiple rows in my database, but all it does is refresh and revert back to its original data. Im guessing my problem is in my update script but  I cant seem to find where I have gone wrong. Any help would be greatly appreciated.
<?php
$host="*****";
$username="*****";
$password="*****";
$db_name="*****";
$tbl_name="*****"; 

// Connect to server and select databse.
$db = new PDO('mysql:host=' . $host . ';dbname=' . $db_name, $username, $password);

// If there is an update, process it.
if (isset($_POST['submit'], $_POST['pageorder']) && is_array($_POST['pageorder'])) {
    $stmt = $db->prepare("UPDATE `$tbl_name` SET `pageorder`=:pageorder WHERE id=:id");
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->bindParam(':pageorder', $pageorder, PDO::PARAM_STR);
    foreach ($_POST['pageorder'] as $id => $pageorder) {
        $stmt->execute();
    }
    echo '<h1>Updated the records.</h1>';
}

// Print the form.
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF']) . '" method="post">';
foreach ($db->query("SELECT `id`, `pageorder`, `pagetitle`, `linklabel` FROM `$tbl_name` ORDER BY `pageorder`") as $row) {
    echo '<input type="text" pageorder="pageorder[' . (int)$row['id'] . ']" value="'. htmlspecialchars($row['pageorder']) . '" />';
    echo '<input type="text" pagetitle="pagetitle[' . (int)$row['id'] . ']" value="'. htmlspecialchars($row['pagetitle']) . '" />';
    echo '<input type="text" linklabel="linklabel[' . (int)$row['id'] . ']" value="'. htmlspecialchars($row['linklabel']) . '" /><br />';
}
echo '<input type="submit" name="submit" value="Update" /></form>';
?> 

Ok I'm trying to insert multiple rows by using a while loop but having problems. At the same time, need to open a new mysql connection while running the insert query, close it then open the previous mysql connection.

I managed to insert multiple queries before using a loop, but for this time, the loop does not work? 
I think it is because I am opening another connection... yh that would make sense actually?

Here is the code:


$users = safe_query("SELECT * FROM ".PREFIX."user");
       while($dp=mysql_fetch_array($users)) { 
          
   $username = $dp['username'];
 $nickname = $dp['nickname'];
 $pwd1 = $dp['password']; 
 $mail = $dp['email'];
 $ip_add = $dp['ip'];
 $wsID = $dp['userID'];
 $registerdate = $dp['registerdate'];
 $birthday = $dp['birthday'];
 $avatar = $dp['avatar'];
 $icq = $dp['icq']; 
 $hp = $dp['homepage'];
 
echo $username." = 1 username only? :(";
      

// ----- Forum Bridge user insert -----

$result = safe_query("SELECT * FROM `".PREFIX."forum`");
$ds=mysql_fetch_array($result);
$forum_prefix = $ds['prefix'];
define(PREFIX_FORUM, $forum_prefix);
define(FORUMREG_DEBUG, 0);

$con = mysql_connect($ds['host'], $ds['user'], $ds['password']) or system_error('ERROR: Can not connect to MySQL-Server');
$condb = mysql_select_db($ds['db'], $con) or system_error('ERROR: Can not connect to database "'.$ds['db'].'"');
                
include('../_phpbb_func.php');
$phpbbpass = phpbb_hash($pwd1);
$phpbbmailhash = phpbb_email_hash($mail);
$phpbbsalt = unique_id();

safe_query("INSERT INTO `".PREFIX_FORUM."users` (`username`, `username_clean`, `user_password`, `user_pass_convert`, `user_email`, `user_email_hash`, `group_id`, `user_type`, `user_regdate`, `user_passchg`, `user_lastvisit`, `user_lastmark`, `user_new`, `user_options`, `user_form_salt`, `user_ip`, `wsID`, `user_birthday`, `user_avatar`, `user_icq`, `user_website`) VALUES ('$username', '$username', '$phpbbpass', '0', '$mail', '$phpbbmailhash', '2', '0', '$registerdate', '$registerdate', '$registerdate', '$registerdate', '1', '230271', '$phpbbsalt', '$ip_add', '$wsID', '$birthday', '$avatar', '$icq', '$hp')");
if (FORUMREG_DEBUG == '1') {
echo "<p><b>-- DEBUG -- : User added: ".mysql_affected_rows($con)."<br />";
       echo "<br />-- DEBUG -- : Query used: ".end($_mysql_querys)."</b></p><br />";
      
       $result = safe_query("SELECT user_id from ".PREFIX_FORUM."users WHERE username = '$username'");
       $phpbbid = mysql_fetch_row($result);
safe_query("INSERT INTO `".PREFIX_FORUM."user_group` (`group_id`, `user_id`, `group_leader`, `user_pending`) VALUES ('2', '$phpbbid[0]', '0', '0')");
safe_query("INSERT INTO `".PREFIX_FORUM."user_group` (`group_id`, `user_id`, `group_leader`, `user_pending`) VALUES ('7', '$phpbbid[0]', '0', '0')");
mysql_close($con);
}

         include('../_mysql.php');        
         mysql_connect($host, $user, $pwd) or system_error('ERROR: Can not connect to MySQL-Server');
mysql_select_db($db) or system_error('ERROR: Can not connect to database "'.$db.'"');
       
       }



So I need to be able to insert these rows using the while loop.. how can I do this?

I really appreciate any help.

I have problem that only last text field  is updated, how should I fix this?

Here's the code
<?php
if(isset($_POST["update"])){
mysql_query("UPDATE categories SET name_category = '".$_POST['category']."'  WHERE ID= ".$_POST['currentCat']." ") or die(mysql_error());
mysql_query("UPDATE podkategorije SET name_subcategory = '".$_POST['subcategory']."'  WHERE id_subCat= ".$_POST['currentSubCat']." ") or die(mysql_error());
 }
?>
<form action="" method="post" >
<?php

//creating texfields from db

$query = "SELECT k.ID, k.name_category, pk.name_subcategory, pk.id_subCat
          FROM `categories` AS k
          JOIN `subcategories` AS pk ON pk.id_mainCat = k.ID";

$result = mysql_query($query) or die(mysql_error());

$currentCat = false;
while($row = mysql_fetch_array($result))
{
    //so it doesn't repeat itself
    if($currentCat != $row['ID'])
    {
        //display of main Categories

?>

<ul>

<li>

<br/><input name="categories" type="text" value="<?php  echo $row['name_category']; ?>" />

</li>

</ul>

<?

$currentCat = $row['ID'];

    }
    //display subcategories

?>

<input name="subcategories" type="text" value="<?php  echo $row['name_category']; ?>" /><br/>

<input type="hidden" name="currentCat" value="<?php echo $row['ID']; ?>" />

<input type="hidden" name="currentSubCat" value="<?php echo $row['id_subCat']; ?>" />

<?
}
?>
<br />
<input type="button" value="Back" onClick="history.go(-1);return true;">
<input type="submit" value="Update" name="update"/>
</form>

Apologies... I'm a noob trying to reverse engineer code.

I have a script that can retrieve values from a MySQL database (based on a session login that recognizes which user you are) and places those values  into the text fields of a form, pre-populating the form.

Code: [Select]
<?php
mysql_connect('localhost', 'db', 'password'); 
mysql_select_db('users');

$id     =  mysql_real_escape_string($_SESSION['userid']);
$select =  "SELECT * FROM user_info WHERE md5(Member_No) = '$id';";
$query  =  mysql_query($select);
if ($row = mysql_fetch_array($query)) {
?>
<form>
FIRST name: <input type="text" name="First" value="<?php echo stripslashes($row['First']); ?>"
LAST name: <input type="text" name="Last"  value="<?php echo stripslashes($row['Last']); ?>" >
Birthday: <input type="text" name="Birthday" value="<?php echo stripslashes($row['Birthday']); ?>"
<input type="submit" value="Submit">
</form>
<?php
}
?>

I want to be able to connect to that same database and update ALL the values (3 in the example above) with the single click of the form button.  I think I was told some time ago that it's not possible to update all the values at once?

Thanks,

~Wayne

Hello everyone,

Once again i need your help this time i want to know that, if I inserting multiple row by single insert query with the help of foreach (loop) then how to insert another cell value according to the name wise

so that's way i send my hole code to you.

<?php
                            //include configuration file for connection
                            include_once('config.php');
                            $sql = "select * from CLASS_STUDENT ORDER BY STUDENT_NAME ASC ";
                            $result=mysql_query($sql);
                            $count=mysql_num_rows($result);
                         ?>
                         <form name="form1" method="post" action="">
                            <table style="text-align: left; padding: 5px;" cellpadding="0px" cellspacing="0px">
                           <tbody>
                              <tr>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Name</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Class</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Section</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Current Date</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Present</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Student Absent</th>
                                 <th style="text-align: center; padding: 5px; border: 1px #000000 solid;">Comment</th>
                              </tr>
                              <?php
                                 while($rows=mysql_fetch_array($result))
                                 {
                              ?>                                                   
                              <tr>                                 
                                 <td class="table1">
                                    <? $id[] = $rows['STUDENT_NAME']; ?><? echo $rows['STUDENT_NAME'];?>
                                 </td>
                                 <td class="table1">
                                    <input name="class[<? echo $rows['STUDENT_NAME']; ?>]" type="text" value="<? echo $rows['STUDENT_CLASS']; ?>">
                                 </td>     
                                 <td class="table1">
                                    <input  name="section[<? echo $rows['STUDENT_NAME']; ?>]" type="text" value="<? echo $rows['STUDENT_SECTION']; ?>">
                                 </td>
                                 <td class="table1">
                                    <input name="date[<? echo $rows['STUDENT_NAME']; ?>]" type="text" value="<? echo $rows['PRESENT_DATE']; ?>">
                                 </td>                                                                                 
                                 <td id="present">
                                    <input type="radio" name="present[<? echo $rows['STUDENT_NAME']; ?>]" checked="checked" value="PRESENT">Present           
                                 </td>
                                 <td id="absent">                                   
                                    <input type="radio" name="present[<? echo $rows['STUDENT_NAME']; ?>]" value="ABSENT">Absent
                                 </td>                             
                                 <td style="text-align: left; padding: 5px; border: 1px #000000 solid; height: 33px;">
                                    <input name="comment[<? echo $rows['STUDENT_NAME']; ?>]" type="text" value="<? echo $rows['COMMENT'];?>">
                                 </td>                                                   
                              </tr>
                              <?php }?>
                              <tr>
                                 <td colspan="7" style="vertical-align:middle; text-align: center;"><br><br>                                   
                                    <input id="Submit" type="submit" name="Submit" value="Insert" style="text-align: center; background-color: #000000; color: #ffffff; border: 1px #000000 solid;">
                                 </td>
                              </tr>                                                                                 
                           </tbody>
                        </table>
                     </form>   
                     <?php
                        if(isset($_POST['Submit']))
                        {
                           foreach($_POST['present'] as $id => $value)
                           {
                              $class=$_POST['class'];
                              $section=$_POST['section'];
                              $date=$_POST['date'];
                              $comment=$_POST['comment'];                                   
                               $sql = "INSERT INTO ATTENDANCE(STUDENT_NAME, STUDENT_CLASS, STUDENT_SECTION, PRESENT_DATE, STUDENT_PRESENT, COMMENT) VALUES ('".$id."', '$class[$value]', '$section[$value]', '$date[$value]', '".$value."', '$comment[$value]') ";
                               $result = mysql_query($sql);                           
                           }
                        }
                        if($result)
                        {
                            header("location:Tea_home.php");                                                     
                        }
                        else
                        {
                           //print_r ($_POST);
                           echo "Your entry is not completed at this time.............";
                        }
                     ?>


My result is comming just like that

STUDENT_ID ||   STUDENT_NAME || STUDENT_CLASS  ||   STUDENT_SECTION  ||   PRESENT_DATE ||   STUDENT_PRESENT    ||COMMENT
231    ||   PRASHANT KUMAR   ||                     ||                       || 1/1/0001 12:00:00 AM       ||         ABSENT                ||   
230    ||   JYOTI NANDA            ||                     ||                       || 1/1/0001 12:00:00 AM       ||            PRESENT        ||   
229    ||   TARUN NANDA          ||                     ||                       || 1/1/0001 12:00:00 AM       ||            PRESENT         ||   
228    ||   RAVI KUMAR             ||                     ||                       || 1/1/0001 12:00:00 AM       ||            PRESENT         ||   
227    ||   RAJIV KUMAR            ||                     ||                       || 1/1/0001 12:00:00 AM       ||            PRESENT           ||
226    ||   PRASHANT KUMAR    ||                     ||                       || 1/1/0001 12:00:00 AM       ||            ABSENT           ||
225    ||   JYOTI NANDA            ||                     ||                       || 1/1/0001 12:00:00 AM       ||            PRESENT           ||



can you help me where i am writing wrong code

I'm trying to echo multiple rows from a MySQL database with no luck.

I've tried this code and many other combinations with it, but can't seem to get a result.
Quote

$query = "select email,mens,womens from newsletters"; $result = mysql_query($query); while ($row = mysql_fetch_assoc($result)) {foreach ($row as .$email. .$mens. .$womens.) {echo "$email - $mens - $womens<BR>";}}


What am I doing wrong?

Hi All,

I'm hoping someone can help me. I'm looking to create a form that will submit multiple entries to a MySQL database depending on the options selected on the form. I am able to submit single entries, but the multiple entire is a bit of a mystery to me.

I have attached an JPG image to show an example of how I would like the form to work.

I have also added the description/explination below to this example:

Example 1            
The form was completed and three access types were selected. Thus Three entries, all with the Same Reference number were captured into the database   
         
Example 2            
The form was completed and Two Access Types were selected. Thus Two entries, all with the Same Reference number were captured into the database            
Example 3            
The form was completed and Four Access Types were selected. Thus Four entries, all with the Same Reference number were captured into the database      

Hi Guys

Just need some advice to go in the right direction.

I'm working on a csv upload script (part of a bigger thing i'm building), so i read in the csv to a multipdimensional array and then build a query that inputs all rows in one query - i read this is the most efficient way to import multiple rows of data at once(rather than multiple insert statements). Just for illustration here's the code i use to build the query so you understand what i'm on about:

Code: [Select]
$sql = "INSERT INTO teams (company, teamname, teamnum, amountraised, country, president) VALUES ";

// $rows is a count of the rows in the csv

for($i=1; $i<$rows; $i++){

$sql.="('{$myarray[$i][0]}','{$myarray[$i][1]}','{$myarray[$i][2]}','{$myarray[$i][3]}','{$myarray[$i][4]}','{$myarray[$i][5]}')";
echo $i . "<br/>";

if($i >= 1 && $i < $rows - 1)
{
$sql.= ",";
}
}

Anyway, the issue is that one of the fields("teamnum") needs to be unique - so i've set this as unique on the table in mysql.

But when i run my query it doesn't import anything if one of the records isn't unique. What i really want is for it to import the ones it can and catch the ones it cant import to present to the user.

So my question is - to acheive the above would i need to rewrite the query so that it inserts each row one at a time, instead of all together?

Or can someone point me in the right direction for a better solution?

Probably something very simple i've missed i am sure...

Thanks chaps!

Hi,
I am having difficulty deleting rows in my table using check boxes. I have all the check boxes displaying for each row and a delete button below the table. However when i click on the delete button it isnt deleting the checked rows, i have displayed the code below that i am using;

Code: [Select]
<?php 
  



$result = mysql_query("SELECT * FROM contact ORDER BY msg_id ASC");

echo "<table border='1'>
<tr>
<th>Delete</th>
<th>Message ID</th>
<th>Name</th>
<th>Email</th>
<th>Message</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  ?>
   <td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $row['del_id']; ?>"></td>
   <?php
  echo "<td>" . $row['msg_id'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['email'] . "</td>";
  echo "<td>" . $row['msg'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
?>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>


<?php
// Check if delete button active, start this 
if(isset($_GET['delete'])) {

for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM contact WHERE id=".$_GET['del_id'];
$result = mysql_query($sql);
}

// if successful redirect to delete_multiple.php 
if($result){

}
}
mysql_close();
?>
Please help
Thank you

I insert data in mysql table row using multiple method : 1#2#3 . 1 is ID of country, 2 is Id of state, 3 is ID of town . now i have this table for real estate listings. for each list(home) i have country/state/town (1#2#3). in country table i have list of country - in country table i have list of state - in country table i have list of town. i need to The number of houses in country / state / town . my mean is :
Code: [Select]
USA [ 13 ] <!-- This Is equal of alabama+alaska+arizona -->
----Alabama [8] <!-- This Is equal of Adamsville+Addison+Akron -->
-------Adamsville [2]
-------Addison[5]
-------Akron[1]
......(list of other City)
----Alaska [ 3 ]
-------Avondale[3]
......(list of other City)
----Arizona [ 2 ]
-------College[2]
......(list of other City)
Lisintg Table :

Code: [Select]
ID -- NAME -- LOCATION -- DATEJOIN -- ACTIVE
 1 -- TEST -- 1#2#3    -- 20110101 -- 1
 2 -- TEST1 -- 1#2#3    -- 20110101 -- 1
 3 -- TEST2 -- 1#3#5    -- 20110101 -- 1
 4 -- TEST3 -- 1#7#6    -- 20110101 -- 1
Country Table :

Code: [Select]
id       -- name 
 1        -- USA 
stats Table :

Code: [Select]
id        -- countryid -- name 
 1        -- 1             -- albama
 2        -- 1             -- alaska
 3        -- 1             -- akron
town Table :
Code: [Select]
id        -- countryid -- statsid -- name 
 1        -- 1             -- 1         -- adamsville
 2        -- 1             -- 1         -- addison
 3        -- 1             -- 1         -- akron
Thanks For Any Help.

Given the below 3 database tables I am trying to construct a SQL query that will give me the following result:

customer_favourites.cust_id
customer_favourites.prod_id OR product.id
product.code
product.product_name
product.hidden
product_ section.section_id (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.catpage (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.relative_order (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)

I currently have....

SELECT customer_favourites.cust_id, customer_favourites.prod_id, product.code, product.product_name, product.hidden, product_section.section_id, product_section.relative_order, product_section.catpage  
FROM customer _favourites  
INNER JOIN product ON customer_favourites.prod_id = product.id  
INNER JOIN product_section ON product_section.product_code = product.code  
WHERE `cust_id` = '17' AND `hidden` = '0' GROUP BY `code`  
ORDER BY `section_id` ASC, `relative_order` ASC, `catpage` ASC LIMIT 0,30

This gives me what I want but only sometimes, at other times it randomly selects any row from the product_section table.

I was hoping that by having the row I want as the last row (most recent added) in the product_section table then it would select that row by default but it is not consistent.

Somehow, I need to be able to specify which row to return in the product_section table, it needs to be the row with the lowest section_id value or it should by the last row (most recent).

Pulling my hair out so any help is gratefully received.  
customer_favourites

id     cust_id   prod_id
70    4            469
product

id        code           product_name    hidden
469    ABC123     My Product        0
product_section

id                  section_id      catpage    product_code    relative_order     recommended
44105         19                   232            ABC123             260                    1
44106          3                     125            ABC123              87                      1
44107          2                     98              ABC123             128                     1
44108          1                    156             ABC123             58                        0

 

Hey guys,

I got another one that i could use some help on.  I have a input form that utilizes a javascript that adds additional rows to my table.  here is a look at what i got.

Code: [Select]
<script type="text/javascript">
function insertRow()
{
    var x = document.getElementById('results_table').insertRow(-1);
    var a = x.insertCell(0);
    var b = x.insertCell(1);
    var c = x.insertCell(2);
    var d = x.insertCell(3);
    var e = x.insertCell(4);

    a.innerHTML="<input type=\"text\" name=\"id\">";
    b.innerHTML="<input type=\"text\" name=\"place\">";
    c.innerHTML="<input type=\"text\" name=\"username\">";
    d.innerHTML="<input type=\"text\" name=\"earnings\">";
    e.innerHTML="<input type=\"button\" value=\"delete\" onClick=\"deleteRow(this)\">";
}

function deleteRow(r)
{
    var i=r.parentNode.parentNode.rowIndex;
    document.getElementById('results_table').deleteRow(i);
}

</script>
this is my code stored in the header of my page.  basically just a button to add a new row and a button in each row to delete rows if needed.

here is a look at my html table, pretty basic...

Code: [Select]
<table id="results_table">

            <th>Event ID</th><th>Place</th><th>Username</th><th>Earnings</th>

            <tr>
                <td><input type="text" name="id"></td><td><input type="text" name="place"></td><td><input type="text" name="username"></td><td><input type="text" name="earnings"></td><td><input type="button" value="delete" onClick="deleteRow(this)"</td>
            </tr>

        </table>
Now what I am hoping to acheive is once i submit all of these rows to the database i will insert each of these rows into their own row in the database.  is this doable?

the structure of my database is:

ResultID (Primary Key)
Place
Earnings
UserID (Foreign Key)
EventID (Foreign Key)


now i think the biggest problem i'm having with submitting data to the database is that in the original HTML form I am typing in the actual username and not the userID. 

so when it comes to processing I need to do a switch of these two things before I run my query.  Should something like this work?

Code: [Select]

$username = $_POST['username'];

$userID = "SELECT userID FROM users WHERE username="$username";

$query = mysql_query($userID);



also i've never tried to submit multiple entries at a time.  maybe i have to create an array somehow in order to capture each rows data.  any thoughts?

as always thanks for the help.

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=351561.0

I want to echo out a few subcategories per main category.
It should be a maximum of 20 entries per column

At this stage I have the following... It echo's the results out in 4 columns and not what I want.
Any suggestions?

$query = "SELECT 
adsubcat.name AS subname, adsubcat.linkname AS sublink, adcat.clinkname AS clink
FROM adsubcat 
JOIN adcat ON adcat.id=adsubcat.catid
WHERE adsubcat.catid='$catid' ORDER BY adsubcat.name ASC";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)) {

$count = 0;

print '<table style="text-align:left;margin-left:0px;width:943px;">'."\n";
while ($row = mysql_fetch_assoc($result)){
$gal = '<a href="'.$root.'/'.$row['clink'].'/'.$row['sublink'].'">'.$row['subname'].'</a>';
if ($count == 0){
print '<tr>';
}
++$count;
print '<td>'.$gal.'</td>';
if ($count == 4){
$count = 0;
print'</tr>'."\n";
}
}
print'</table>'."\n";
}

Hi, I am trying to create an admin page for a local Gym Club to allow an Administrator to be able to update club prices which then show on different screens on the site.   I have been able to display the "Prices Admin" page which basically reads a MySQL database table (pricelist), display the description, member price and non-member price and allows the user to update any of these fields.   My problem is that when I try writing the data back to the database with the "UPDATE" statement it fails, what I mean is that nothing updates.  I have at the moment commented out the actual update statement and put in a "file_put_contents" command" to try and work out what is in the various fields before the UPDATE is run.  I find that the display/INPUT works perfectly well but when I do a foreach on the $record array variable I am getting strange results.   I may not have explained this very well but here are the relevant sections of my code;   // Display and Input section   <div id="admin-area">