PHP insert or update mysql table

That statement should be updating every single row in your table.  It should not do an insert.

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?

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?

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.

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());

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());
}

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

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) ");
}

Select allOpen in new window

THanks

"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";

Select allOpen in new window

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/

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.

Thanks