MySQL/PHP not updating database, why?

PHP
1

Hi

Can anyone please tel me why my code is not updating my MySQL database?
The code does not return any errors. It does how ever tell me that the update is successful, but there is no update.

Pelase let me know should you require to view the code running prior to this

<?phpsession_start();?> <?php $ud_ref=$_POST['ud_ref'];$ud_date_time=$_POST['ud_date_time'];$ud_customer_name=$_POST['ud_customer_name'];$ud_customer_email_address=$_POST['ud_customer_email_address'];$ud_case_number=$_POST['ud_case_number'];$ud_msisdn=$_POST['ud_msisdn'];$ud_route_cause=$_POST['ud_route_cause'];$ud_escalatedto=$_POST['ud_escalatedto'];$ud_province=$_POST['ud_province'];$ud_comments=$_POST['ud_comments'];    if ($ud_ref == "") echo "! No identifier retrieved";elseecho "<p>Amending record $ud_ref"; //clean up any carriage returns etc$ud_ref = preg_replace("/[\
\\r]*/","",$ud_ref);$ud_date_time = preg_replace("/[\
\\r]*/","",$ud_date_time);$ud_customer_name = preg_replace("/[\
\\r]*/","",$ud_customer_name);$ud_customer_email_address = preg_replace("/[\
\\r]*/","",$ud_customer_email_address);$ud_case_number = preg_replace("/[\
\\r]*/","",$ud_case_number);$ud_msisdn = preg_replace("/[\
\\r]*/","",$ud_msisdn);$ud_route_cause = preg_replace("/[\
\\r]*/","",$ud_route_cause);$ud_escalatedto = preg_replace("/[\
\\r]*/","",$ud_escalatedto);$ud_province = preg_replace("/[\
\\r]*/","",$ud_province);$ud_comments = preg_replace("/[\
\\r]*/","",$ud_comments);  $host = "localhost";$login_name = "root";$password = "password";  //Connecting to MYSQLMySQL_connect("$host","$login_name","$password"); //Select the database we want to usemysql_select_db("csops") or die("Could not select database");  mysql_query("UPDATE scrubs_tracker SET ref='$ud_ref', date_time='$ud_date_time', customer_name='$ud_customer_name', customer_email_address='$ud_customer_email_address', case_number='$ud_case_number', msisdn='$ud_msisdn', route_cause='$ud_route_cause', escalatedto='$ud_escalatedto', province='$ud_province', comments='$ud_comments', WHERE ref='$ref'");  echo "<BR>Record $ref <-- Updated<BR><BR>";include "record_updater_scrubs.php"; ?><?php
session_start();
?>

<?php

$ud_ref=$_POST['ud_ref'];
$ud_date_time=$_POST['ud_date_time'];
$ud_customer_name=$_POST['ud_customer_name'];
$ud_customer_email_address=$_POST['ud_customer_email_address'];
$ud_case_number=$_POST['ud_case_number'];
$ud_msisdn=$_POST['ud_msisdn'];
$ud_route_cause=$_POST['ud_route_cause'];
$ud_escalatedto=$_POST['ud_escalatedto'];
$ud_province=$_POST['ud_province'];
$ud_comments=$_POST['ud_comments'];




if ($ud_ref == "") echo "! No identifier retrieved";
else
echo "<p>Amending record $ud_ref";

//clean up any carriage returns etc
$ud_ref = preg_replace("/[\
\\r]*/","",$ud_ref);
$ud_date_time = preg_replace("/[\
\\r]*/","",$ud_date_time);
$ud_customer_name = preg_replace("/[\
\\r]*/","",$ud_customer_name);
$ud_customer_email_address = preg_replace("/[\
\\r]*/","",$ud_customer_email_address);
$ud_case_number = preg_replace("/[\
\\r]*/","",$ud_case_number);
$ud_msisdn = preg_replace("/[\
\\r]*/","",$ud_msisdn);
$ud_route_cause = preg_replace("/[\
\\r]*/","",$ud_route_cause);
$ud_escalatedto = preg_replace("/[\
\\r]*/","",$ud_escalatedto);
$ud_province = preg_replace("/[\
\\r]*/","",$ud_province);
$ud_comments = preg_replace("/[\
\\r]*/","",$ud_comments);


$host = "localhost";
$login_name = "root";
$password = "password";


//Connecting to MYSQL
MySQL_connect("$host","$login_name","$password");

//Select the database we want to use
mysql_select_db("csops") or die("Could not select database");


mysql_query("UPDATE scrubs_tracker 
SET 
ref='$ud_ref'
, date_time='$ud_date_time'
, customer_name='$ud_customer_name'
, customer_email_address='$ud_customer_email_address'
, case_number='$ud_case_number'
, msisdn='$ud_msisdn'
, route_cause='$ud_route_cause'
, escalatedto='$ud_escalatedto'
, province='$ud_province'
, comments='$ud_comments'
, WHERE ref='$ref'");


echo "<BR>Record $ref <-- Updated<BR><BR>";
include "record_updater_scrubs.php";

?>
2

Will this carry over the ID from the usrinfo table throughout their session in the site?

I have had a suggestion of using something like this

<?php
      if (isset($_COOKIE["user"]))
      {
                  setcookie("user",$_COOKIE["user"],time()+3600);
                  $admin = 1;
      } else {
      $_COOKIE["user"];
      }
      
      $user = $_COOKIE["user"];
?>

I am just not sure of how to implement it on all my pages.

3 
 int mysql_insert_id  ([  resource $link_identifier  ] )

Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).

About login functional you cen read this Managing Users with PHP Sessions and MySQL

4

I think should not be

$ref

and be so

$ud_ref

… or i’m wrong…

5

Ok thanks a million guys I got it to work. I really apprciate all the help and time you guys put into helping me with this.

I now just have one more functionality of my site that isn’t working. And that is the ability to store the auto incremented field from the userinfo table into the table of the form that is being submitted. And also to only allow access to my pages when the users have logged in. What info/code that I have worked on do you need from me to help me through this?

6

heidisql, sqlyog, phpmyadmin, mysql query browser, the mysql command line…

7

it’s the dangling comma in front of the WHERE clause

would’ve been found immediately if you had tested the query outside of php first :slight_smile:

also, this thread confirms my position that the leading comma convention makes errors easier to see

8

Print your UPDATE query

echo $query " ...

check syntax end try to testing it outside php in command prompt or if you have instaled in PMA.

9

Just some general advice…

Why are you using session_start() twice?

Why are you assigning variable names to $_POST values twice?

Try the following, although it is untested…

Replace this :


MySQL_connect("$host","$login_name","$password"); 

//Select the database we want to use 
mysql_select_db("csops") or die("Could not select database");

with


$link = mysql_connect("$host","$login_name","$password"); 

if (!$link) {
    die('Not connected : ' . mysql_error());
}

$db_selected = mysql_select_db('csops', $link);
if (!$db_selected) {
    die ('Can\\'t use csops: ' . mysql_error());
}
10

If you eliminated the comma like r937 said, and you added the code wackyjoe and I gave you, and you added the ‘echo $query;’ as well (without ’ :wink: ), then you should have some output. An error maybe? Or at least the query as it is being executed.
Post the result you see in the browser window here.

11

Hi, I removed the comma, but it also doesn’t work.

On your refernce to testing it outside php, what tools can I use to do this? I hard coded this in Notepad ++ and only did the appearance using Dreamweaver.

12

Sorry I have to apologise to guido2004, I missed a part of the code that you said I should use to replace mine. The code works just. Only thing is my database is still not updating… What could I be missing?

I must admit I really do feel dumb over here. I have attemped a php project and I’m struggling really badly. Is not easy to learn this stuff by yourself, so please bear with me if I ask stupid questions

13

HI guys,

Thanks so much for everyone heres help, but thus far I still don’t have a worknig solution. Would it maybe help if I upload all my code to the whole update?

14

Thanks I replaced the code that you suggested I rplace, although my database still doesn’t update. The duplicte session input was an error in my posting, and as for the variable names that I assigned to $_Post, I honestly don’t know why I did that. I am still very new to PHP and MySQL so learning a lot at the moment.

guido2004, I also tried updating my code with te code you supplied but I then get a HTTP 500 error screen

Its really difficult though when I don’t have anyone who can really show me the ropes. I really appreciate your help

15

Hi there,

this is the result the query gives on screen:

UPDATE scrubs_tracker 
SET 
ref='2' 
, date_time='2010-06-03 17:28:22' 
, customer_name='Frediek' 
, customer_email_address='N/A' 
, case_number='2510308' 
, msisdn='741928755' 
, route_cause='Service Activations' 
, escalatedto='Scrubs' 
, province='' 
, comments='' 
WHERE ref=''

Am I correct in saying that the where “ref” part isn’t working? What do I need to do to fix this?

16

You were right, thats exactly what I did as you were replying to my post :cool: YOU GUYS ARE AWESOME

17

And replace this


mysql_query("UPDATE scrubs_tracker 
SET 
ref='$ud_ref'
, date_time='$ud_date_time'
, customer_name='$ud_customer_name'
, customer_email_address='$ud_customer_email_address'
, case_number='$ud_case_number'
, msisdn='$ud_msisdn'
, route_cause='$ud_route_cause'
, escalatedto='$ud_escalatedto'
, province='$ud_province'
, comments='$ud_comments'
, WHERE ref='$ref'");

with


$query = "
  UPDATE scrubs_tracker 
  SET 
    ref='$ud_ref'
  , date_time='$ud_date_time'
  , customer_name='$ud_customer_name'
  , customer_email_address='$ud_customer_email_address'
  , case_number='$ud_case_number'
  , msisdn='$ud_msisdn'
  , route_cause='$ud_route_cause'
  , escalatedto='$ud_escalatedto'
  , province='$ud_province'
  , comments='$ud_comments'
  , WHERE ref='$ref'
";
mysql_query($query) or die('mysql error ' . mysql_error() . ' in query : ' . $query);

And just to debug a bit more, you should also echo out the value of $query, so you can check and see if it contains all the values you are expecting.