[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP insert or update mysql table

Posted on 2011-10-31
13
Medium Priority
?
214 Views
Last Modified: 2012-05-12
Hi all,

I have a small problem with some code.
The code works fine but it's only creating 1 record on the table and I have multiple users so it sounld be creating a new record if the use is new.

It's a basic insert/update into the mysql table but what I want to do is to use UPDATE to update changes if use has already a record or INSERT if it's a new user.

Right now I have this to insert the form data into the table:

mysql_query("UPDATE myTable SET 
id='',
user_id='$user_id',
title='$title',
published='$published';
") or die(mysql_error());

Open in new window


The proble with the code above is that it only works for 1 user record.

Can anyone help please?

THanks
0
Comment
Question by:error77
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37055280
That statement should be updating every single row in your table.  It should not do an insert.
0
 

Author Comment

by:error77
ID: 37055317
The problem is that all users have the same fields but when I test the code on a different user_id it's just updating the 1 record and changing the user_id field instead of having a record per user_id.

So, you are saying I should change the sql code?
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37055474
I'm saying you could trash your entire table if that was working right.  Do you know how many rows you actually have in you table?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

by:Amar Bardoliwala
ID: 37055606
Hello error77,

problem you are trying to get is not much clear here.

Also if you can put whole code here will help experts here to solve your problem.

Code you have given is only update query without any loop for users.

Also update query will only update record and will never insert new record.

For your problem you need to do following

1. check if record exists for user. If record exists run update query.
2. If record does not exists, run Insert query.

Hope this will help.

Thank You.

Amar.

0
 
LVL 10

Expert Comment

by:ukerandi
ID: 37056005
i didn't understand you your question 100% but i assume you need like this

mysql_query("UPDATE myTable SET
id='',
user_id='$user_id',
title='$title',
published='$published' WHERE user_id='$user_id'
") or die(mysql_error());
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 37056044
According to Amar suggest

1. check if record exists for user. If record exists run update query.
if ($user_id<>'')
{
mysql_query("UPDATE myTable SET
id='',
user_id='$user_id',
title='$title',
published='$published' WHERE user_id='$user_id'
") or die(mysql_error());
}
2. If record does not exists, run Insert query.
$id='';
if ($user_id='')
{


mysql_query("INSERT INTO myTable (id,user_id,title,published) values('".$id."','".$user_id."','".$title."','".$published."'") or die(mysql_error());
}
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37056626
Grab a copy of this book.  It will not make you a pro, but it will help you get a grip on things like this query set.
http://www.sitepoint.com/books/phpmysql4/

A MySQL query applies to ALL ROWS in a table by default.  If you do not want it to apply to all rows, you must use qualifying clauses such as WHERE or LIMIT to restrict the query.  Example:  DELETE FROM mytable.  Poof, all your data is gone.  But if you said, DELETE FROM mytable WHERE id=3 you would only remove the rows with 3 in the id column.

MySQL has a non-standard extension, "REPLACE INTO" that might be helpful for your application.
http://dev.mysql.com/doc/refman/5.0/en/replace.html
0
 

Author Comment

by:error77
ID: 37056981
Almost there...this is what I need but its not working ... anyone please?

Here's the code:

if(mysql_num_rows(mysql_query("SELECT user_id FROM myTable WHERE user_id = '$user_id'"))){

	mysql_query("UPDATE myTable SET
	id='',
	user_id='$user_id',
	title='$title',
	WHERE user_id='$user_id'
	") 
	
}  else {
	
	mysql_query("INSERT INTO myTable (id, user_id, title)
				 VALUES ('', $user_id, $title) ");
}

Open in new window


THanks
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37057540
"not working" doesn't tell us much.  You might want to test the queries for success.  MySQL is not a black box.  It can and will fail for reasons that may be outside of your program's control, and you will want to know about that.  The code snippet shows how to do some of the basics in MySQL.  Note that whenever an error occurs, the MySQL functions return FALSE. Then you will need to inspect the return values from mysql_errno() and mysql_error() to decide what should happen next.

One sure way to make it hard to debug a program - use compound statements like this:

if(mysql_num_rows(mysql_query("SELECT user_id FROM myTable WHERE user_id = '$user_id'"))){

The reasons that this is poor form are many, but here are a few.  (1) We cannot print out the query string because it is constructed in the function call.  (2) We cannot know whether the mysql_query() function worked or failed because the code does not test for success.  (3) We do not know the input to mysql_num_rows() because of #2. (4) What just happened?  No clue, sorry.

On line 13 we have this:
    VALUES ('', $user_id, $title) ");

Almost certainly you want to learn about mysql-real_escape_string() and you want to use quotes around the field values.  And test the query for success.
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

0
 

Author Comment

by:error77
ID: 37057803
Ray_Paseur ... There's no need to be so agressive.

I know you don't get paid for helping here and I appreciate the help but I pay every month for asking questions here and don't mind but ... you know...no need for agressiveness :o/
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37057862
Not being aggressive, just trying to show the best ways to do some things.  I've made enough mistakes in queries that I know I need to be able to see the fully resolved query strings along with the error messages.  Without that information I would be guessing at what might be wrong.  It's almost always a faster and more accurate approach to debugging if you can look at the data.  If you follow the code patterns in the snippet I posted above, you will get useful error messages whenever something goes wrong.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 37057944
This is untested, but it shows how I might write the code...  It looks like a lot of lines of programming, and that is true, but if you have every statement on a separate line you will find the programming is easier to debug and maintain.
<?php // RAY_temp_error77.php
error_reporting(E_ALL);



// ASSUMPTION: SUCCESSFUL CONNECT AND SELECT TO THE DATA BASE
// ASSUMPTION: VARIABLES $user_id AND $title ARE BOTH DEFINED AND HAVE PASSED SANITY CHECKS



// PREPARE THE DATA FOR USE IN A QUERY
$safe_user_id = mysql_real_escape_string($user_id);
$safe_title   = mysql_real_escape_string($title);


// CONSTRUCT A QUERY AND RUN IT - OR DIE IF THERE IS AN ERROR
$sql = "SELECT user_id FROM myTable WHERE user_id = '$safe_user_id'";
$res = mysql_query($sql);
if (!$res)
{
    $msg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAILED: $sql<br/>";
    die($msg);
}

// DID WE FIND A MATCH?
$num = mysql_num_rows($res);
if ($num)
{
    // PROBABLY THIS SHOULD HAVE A LIMIT CLAUSE?  NOT SURE ABOUT THAT.  NO NEED TO UPDATE THE user_id COLUMN
    $sql = "UPDATE myTable SET id='', title='$safe_title' WHERE user_id='$safe_user_id'";
    $res = mysql_query($sql);
	if (!$res)
	{
	    $msg = mysql_errno() . ' ' . mysql_error();
	    echo "<br/>QUERY FAILED: $sql<br/>";
	    die($msg);
	}
}
else
{
    // NO NEED TO INSERT A BLANK VALUE FOR id -- JUST DEFINE THE CORRECT DEFAULT VALUE FOR THE COLUMN
    $sql = "INSERT INTO myTable (user_id, title) VALUES ('$safe_user_id', '$safe_title')";
    $res = mysql_query($sql);
	if (!$res)
	{
	    $msg = mysql_errno() . ' ' . mysql_error();
	    echo "<br/>QUERY FAILED: $sql<br/>";
	    die($msg);
	}
}

Open in new window

0
 

Author Closing Comment

by:error77
ID: 37059567
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month19 days, 17 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question