6 Replies - 20702 Views - Last Post: 08 November 2007 - 06:17 PM Rate Topic: -----

#1 houmic48  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-October 07

update mysql using php

Post icon  Posted 24 October 2007 - 12:37 AM

I am trying to update a table in mysql. the first page shows all the info from the table and has a check box so you can select what field to update. i don't seem to have any problems with it. the second page doesn't do anything. it just stays blank. i been messing around trying to figure it out for a while and can't seem to get it. at one point i had it connecting but it still wouldn't update the database, even though it would it has. so i have no idea. feel free to suggest anything.

<?php
include ( 'dbconnect.php' );

$query =" select * from customer ";

$result = mysqli_query($db, $query)
	  or die ("  can not find data !!! ");

echo "<h1> here is the data </h1>\n";

echo "<form action='mod1pinfo.php'>\n";
$ctr = 0;
$row = mysqli_fetch_row($result);
 echo "<table border='6' cellpadding='10'> ";

  
  while ($row) {
   echo "<tr>";
	echo "<td> <input type='checkbox' name='mod$ctr'>\n";
	echo "<input type='text' name='customerid$ctr' value='$row[0]'></td>  \n";
	echo "<td><input type='text' name='fname$ctr'  value='$row[1]'></td>\n";
	echo "<td><input type='text' name='lname$ctr'  value='$row[2]'> </td> ";
	echo "<td><input type='text' name='pnumber1$ctr' value='$row[3]'> </td> \n";
	echo "<td><input type='text' name='pnumber2$ctr'	value=' $row[4]'> </td> \n";
	echo "<td><input type='text' name='pnumber3$ctr' value='$row[5]'> </td> \n";
	echo "<td><input type='text' name='address$ctr' value='$row[6]'> </td> \n";
	echo "<td><input type='text' name='City$ctr' value='$row[7]'> </td> \n";
	echo "<td><input type='text' name='State$ctr' value='$row[8]'> </td> \n";
	echo "<td><input type='text' name='zipcode$ctr' value='$row[9]'> </td> \n";
	echo "<td><input type='text' name='shipping$ctr' value='$row[10]'> </td> \n";
	echo "<td><input type='text' name='uname$ctr' value='$row[11]'> </td> \n";
	echo "<td><input type='text' name='pword$ctr' value='$row[12]'> </td> \n";
		$row = mysqli_fetch_row($result);
   echo " </tr>\n\n";

  $ctr++;

  }

 echo "<input type='hidden' name='ctotal' value='$ctr'> \n"; 
  echo "<tr><td><input type='submit'></td></tr>\n";
  echo "</table>  </form>";
//=========================================

include ('endofpageinclude.php');
?>




include ( 'dbconnect.php' );  //$db

$ctr=0;
$ctotal = $_REQUEST['ctotal'];
echo $ctotal;


 while($ctr < $ctotal ) {

  if ($_REQUEST['mod'.$ctr] == 'on')
 {	  
echo "<br> $ctr was checked\n";
	 $customerid = $_REQUEST['customerid'.$ctr];
	 $fname	  = $_REQUEST['fname'.$ctr];
	 $lname	  = $_REQUEST['lname'.$ctr];
	 $pnumber1   = $_REQUEST['pnumber1'.$ctr];
	 $pnumber2   = $_REQUEST['pnumber2'.$ctr];
	 $pnumber3   = $_REQUEST['pnumber3'.$ctr];
	 $address	= $_REQUEST['address'.$ctr];
	 $City	   = $_REQUEST['City'.$ctr];
	 $State	  = $_REQUEST['State'.$ctr];
	 $zipcode	= $_REQUEST['zipcode'.$ctr];
	 $shipping   = $_REQUEST['shipping'.$ctr];
	 $uname	  = $_REQUEST['uname'.$ctr];
	 $pword	  = $_REQUEST['pword'.$ctr];
  
}

	  }		 // end while
//Define query

$query = "UPDATE customer SET fname='$fname', lname='$lname', pnumber1='$pnumber1', pnumber2='$pnumber2', pnumber3='$pnumber3', address='$address', City='$City', State='$State', zipcode='$zipcode', shipping='$shipping', uname='$uname', pword='$pword' WHERE customerid='$customerid'";
  
   //executes the query
$results = mysql_query ($query);

   
   $ctr++;
			  
 else {		  //ignore the stuff
   echo "<h2> $modcounter records updated </h2> <br>";
}
  include ('endofpageinclude.php');
?>



[mod edit] Thank you for using code tags. I just cleaned it up. :)

Is This A Good Question/Topic? 0
  • +

Replies To: update mysql using php

#2 Martyr2  Icon User is online

  • Programming Theoretician
  • member icon

Reputation: 4421
  • View blog
  • Posts: 12,286
  • Joined: 18-April 07

Re: update mysql using php

Posted 24 October 2007 - 11:11 AM

You have a few problems here. First of all, your $ctr++; is not in your while loop so it will never get incremented and sit in an infinite loop. Secondly you have an else at the bottom which appears not to be following an if statement. The if statement is in the loop and the else is outside the while loop sitting there without an if. If your blank page is also appearing to be waiting for the site (waiting forever), you know it is the infinite loop at work hanging the browser.

Also make sure that all the fields in your database table customer are of type varchar or some variation of strings because according to your query you make every field a string including customer id which is usually setup as an integer. if it is an integer, your query will also fail. (you put your single quotes only on field values that are to be evaluated as a string or in some cases a date)

Also make sure you have error displays on or being logged somehow so when an error does happen you will see it.

So make those corrections and see what else you get. :)

This post has been edited by Martyr2: 24 October 2007 - 11:12 AM

Was This Post Helpful? 0
  • +
  • -

#3 houmic48  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-October 07

Re: update mysql using php

Posted 31 October 2007 - 10:55 PM

Thank that helped. now it prints out records updated a bunch of times. it doesn't change any of the information in the database though. from what i can see its not running the if statement, its just running the else statement. if u have any suggestions i would apperciate it.

View PostMartyr2, on 24 Oct, 2007 - 11:11 AM, said:

You have a few problems here. First of all, your $ctr++; is not in your while loop so it will never get incremented and sit in an infinite loop. Secondly you have an else at the bottom which appears not to be following an if statement. The if statement is in the loop and the else is outside the while loop sitting there without an if. If your blank page is also appearing to be waiting for the site (waiting forever), you know it is the infinite loop at work hanging the browser.

Also make sure that all the fields in your database table customer are of type varchar or some variation of strings because according to your query you make every field a string including customer id which is usually setup as an integer. if it is an integer, your query will also fail. (you put your single quotes only on field values that are to be evaluated as a string or in some cases a date)

Also make sure you have error displays on or being logged somehow so when an error does happen you will see it.

So make those corrections and see what else you get. :)

Was This Post Helpful? 0
  • +
  • -

#4 mocker  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 50
  • View blog
  • Posts: 466
  • Joined: 14-October 07

Re: update mysql using php

Posted 01 November 2007 - 05:11 AM

You have no error checking at all, so it is hard to see where the problem is without checking the actual data. There are a number of holes in your script. You might want to post your updated script with the changes Martyr2 suggested.

This part is also a little iffy:

echo "<td> <input type='checkbox' name='mod$ctr'>\n";

then :
if ($_REQUEST['mod'.$ctr] == 'on')

I see nowhere with the value 'on' . I am not sure what you are checking there, but it won't validate, thus it will not fill in your values for your update statement.

On the second page you also have your update statement outside of the while loop. This means that no matter how many fields you try to update, it will only do the last one.

Whenever doing anything at all with the database, ESPECIALLY if it comes from input into the page, always validate the incoming data, and check for errors before sending it to the database. If this is your script it is wide open to exploit and someone could do what they want to your database through this script. Even if this is just practice and will not be used somewhere, it is a good idea to always always sanitize your data and check for errors. It will also help you debug if you can see that "oh yea, none of the variables in this update statement are getting set, guess i'll look at that section".
Was This Post Helpful? 0
  • +
  • -

#5 fxitsolutions  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 01-October 07

Re: update mysql using php

Posted 02 November 2007 - 08:44 AM

Try:

<?php
include ( 'dbconnect.php' );

$query =" select * from customer ";

$result = mysqli_query($db, $query)
	  or die ("  can not find data !!! ");

echo "<h1> here is the data </h1>\n";

echo "<form action='mod1pinfo.php'>\n";
$ctr = 0;

echo "<table border='6' cellpadding='10'> ";
  
  while ($row = mysqli_fetch_row($result)) {
   echo "<tr>";
	echo "<td> <input type='checkbox' name='mod$ctr'>\n";
	echo "<input type='text' name='customerid$ctr' value='$row[0]'></td>  \n";
	echo "<td><input type='text' name='fname$ctr'  value='$row[1]'></td>\n";
	echo "<td><input type='text' name='lname$ctr'  value='$row[2]'> </td> ";
	echo "<td><input type='text' name='pnumber1$ctr' value='$row[3]'> </td> \n";
	echo "<td><input type='text' name='pnumber2$ctr'	value=' $row[4]'> </td> \n";
	echo "<td><input type='text' name='pnumber3$ctr' value='$row[5]'> </td> \n";
	echo "<td><input type='text' name='address$ctr' value='$row[6]'> </td> \n";
	echo "<td><input type='text' name='City$ctr' value='$row[7]'> </td> \n";
	echo "<td><input type='text' name='State$ctr' value='$row[8]'> </td> \n";
	echo "<td><input type='text' name='zipcode$ctr' value='$row[9]'> </td> \n";
	echo "<td><input type='text' name='shipping$ctr' value='$row[10]'> </td> \n";
	echo "<td><input type='text' name='uname$ctr' value='$row[11]'> </td> \n";
	echo "<td><input type='text' name='pword$ctr' value='$row[12]'> </td> \n";
		$row = mysqli_fetch_row($result);
   echo " </tr>\n\n";

  $ctr++;

  }

echo "<input type='hidden' name='ctotal' value='$ctr'> \n";
  echo "<tr><td><input type='submit'></td></tr>\n";
  echo "</table>  </form>";
//=========================================

include ('endofpageinclude.php');
?>


This post has been edited by fxitsolutions: 02 November 2007 - 08:45 AM

Was This Post Helpful? 0
  • +
  • -

#6 houmic48  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-October 07

Re: update mysql using php

Posted 07 November 2007 - 09:51 PM

Its just part of a college assignment that I am having a problem with. By the end of the year for the final assignment it has to a fully working website with all the bells and whistles. sorry if any of my questions seem stupid but I appreciate the help.
trying to find out which ones have been checked "if ($_REQUEST['mod'.$ctr] == 'on')"

<?php
include ( 'dbconnect.php' );

$query =" select * from customer ";

$result = mysqli_query($db, $query)
	  or die ("  can not find data !!! ");

echo "<h1> here is the data </h1>\n";

echo "<form action='mod1pinfo.php'>\n";
$ctr = 0;
$row = mysqli_fetch_row($result);
 echo "<table border='6' cellpadding='10'> ";

  
  while ($row=mysqli_fetch_row($result)) {
   echo "<tr>";
	echo "<td> <input type='checkbox' name='mod$ctr' >\n";
	echo "<td><input type='text' name='customerid$ctr' value='$row[0]'></td>  \n";
	echo "<td><input type='text' name='fname$ctr'  value='$row[1]'></td>\n";
	echo "<td><input type='text' name='lname$ctr'  value='$row[2]'> </td> ";
	echo "<td><input type='text' name='pnumber1$ctr' value='$row[3]'> </td> \n";
	echo "<td><input type='text' name='pnumber2$ctr'	value=' $row[4]'> </td> \n";
	echo "<td><input type='text' name='pnumber3$ctr' value='$row[5]'> </td> \n";
	echo "<td><input type='text' name='address$ctr' value='$row[6]'> </td> \n";
	echo "<td><input type='text' name='City$ctr' value='$row[7]'> </td> \n";
	echo "<td><input type='text' name='State$ctr' value='$row[8]'> </td> \n";
	echo "<td><input type='text' name='zipcode$ctr' value='$row[9]'> </td> \n";
	echo "<td><input type='text' name='shipping$ctr' value='$row[10]'> </td> \n";
	echo "<td><input type='text' name='uname$ctr' value='$row[11]'> </td> \n";
	echo "<td><input type='text' name='pword$ctr' value='$row[12]'> </td> \n";
		$row = mysqli_fetch_row($result);
   echo " </tr>\n\n";

  $ctr++;

  }




 echo "<input type='hidden' name='ctotal' value='$ctr'> \n"; 
  echo "<tr><td><input type='submit'></td></tr>\n";
  echo "</table>  </form>";
//=========================================

include ('endofpageinclude.php');
?>


<?php



include ( 'dbconnect.php' );  //$db



$ctr=0;
$ctotal = $_REQUEST['ctotal'];
echo $ctotal;


 while($ctr < $ctotal ) {

  if ($_REQUEST['mod'.$ctr] == 'on')
 {	  
echo "<br> $ctr was checked\n";
	 $customerid = $_REQUEST['customerid'.$ctr];
	 $fname	  = $_REQUEST['fname'.$ctr];
	 $lname	  = $_REQUEST['lname'.$ctr];
	 $pnumber1   = $_REQUEST['pnumber1'.$ctr];
	 $pnumber2   = $_REQUEST['pnumber2'.$ctr];
	 $pnumber3   = $_REQUEST['pnumber3'.$ctr];
	 $address	= $_REQUEST['address'.$ctr];
	 $City	   = $_REQUEST['City'.$ctr];
	 $State	  = $_REQUEST['State'.$ctr];
	 $zipcode	= $_REQUEST['zipcode'.$ctr];
	 $shipping   = $_REQUEST['shipping'.$ctr];
	 $uname	  = $_REQUEST['uname'.$ctr];
	 $pword	  = $_REQUEST['pword'.$ctr];

  
 $ctr++;

$query = "UPDATE customer SET fname='$fname', lname='$lname', pnumber1=$pnumber1, pnumber2=$pnumber2, pnumber3=$pnumber3, address='$address', City='$City', State='$State', zipcode=$zipcode, shipping='$shipping', uname='$uname', pword='$pword' WHERE customerid=$customerid";
  
   //executes the query
$results = mysql_query ($query);


}	// end if


			  

 else {		  //ignore the stuff
   echo "<h2> $modcounter records updated </h2> <br>";
}	//end else
	}	//end while



  include ('endofpageinclude.php');
?>


Was This Post Helpful? 0
  • +
  • -

#7 houmic48  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-October 07

Re: update mysql using php

Posted 08 November 2007 - 06:17 PM

ok I changed a little of my code and it seems to have helped. when I go to the second page to update the database it prints out this

you are connected7
0 was checked UPDATE customer SET fname='mke', lname='houser', pnumber1='814', pnumber2='486', pnumber3='3623', address='4747 rt 46', City='emporium', State='PA', zipcode='15834', shipping='standard', uname='doug', pword='death' WHERE customerid='2'
records updated


records updated


records updated

it just keeps repeating "records updated" a bunch of times then stops.


I echoed the query and it seems to get the info for whatever I want to change. The thing I changed this
" if ($_REQUEST['mod'.$ctr] == 'on')"
to this
" if ($_REQUEST['mod'.$ctr] )"

the problen now is that it still doesn't seem to be changing anything in the actual database. I'm not sure why it isn't because I know all the fields are matched with the database. I gonna keep messin with it to see if I can figure it out and if so I will post back. I will check back here to see what you guy suggest also. thanks for the help
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1