Help - Search - Members - Calendar
Full Version: If Row Exists Update Else Insert ?
Zymic Webmaster Forums > Web Design & Development > Server Side Scripting > PHP
MrTouz
Dec 24 2008, 12:20 AM
Boooo, i am so tired but i promised my self i'd finish tonight ! (it's the morning i know...)

Ok my previous script to update did not work... i fixed it (don't ask me how !)

Here :

CODE
<?php
include ('include/connect.php');

foreach($_POST as $pn => $p) {
    if(empty($p))
        $p = '--EMPTY--';
    $p = str_replace('"', '\\"', $p);
    $p = str_replace("'", "\\'", $p);
}
$sql=("UPDATE users_info SET
username='". $_POST['username'] ."',
sexe='". $_POST['sexe'] ."',
ddn='". $_POST['ddn'] ."',
taille='". $_POST['taille'] ."'
WHERE username = '$session->username' ");
  
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
header("Location: index.php?id=modif_cmpt");

?>


I know some stuff are wrong (thanks alex) but it works... it could be better.

I want to add one feature to this script, if raw exist than update... else insert.

How do i do that... i dunno how to build it up... i could copy if i saw but just can't build.

Sorry if i am making to many freakin threads but its a whole new topic... i need help !!!!!!
MrTouz
Dec 24 2008, 01:09 AM
Ive tried this :

CODE
if (mysql_affected_rows()== 0) {
    $sql="INSERT INTO users_info (username, sexe, ddn, taille, codepostal, corpulance, interesse, facebook, myspace, skyblog, activites, musiques, films, tv, livre, citation, propos, recherche)
values ('$_POST[username]','$_POST[sexe]','$_POST[ddn]','$_POST[taille]','$_POST[codepostal]','$_POST[corpulance]','$_POST[interesse]','$_POST[facebook]','$_POST[myspace]','$_POST[skyblog]','$_POST[activites]','$_POST[musiques]','$_POST[films]','$_POST[tv]','$_POST[livre]','$_POST[citation]','$_POST[propos]','$_POST[recherche]')";
}


If no rows were affected than insert :/ Unfortunately it did not work, i also tried to use the ON DUPLICATE KEY UPDATE but my fields are not all primery keys or unique. Dunno if i can set them all (18 of em) to unique ? to make it work... if it does even make a difference ?
Jetteh22
Dec 24 2008, 02:34 AM
Try this:

CODE
<?php
include ('include/connect.php');

foreach($_POST as $pn => $p) {
    if(empty($p))
        $p = '--EMPTY--';
    $p = str_replace('"', '\\"', $p);
    $p = str_replace("'", "\\'", $p);
}

$sql = "SELECT * FROM users_info WHERE username=\"$session->username\"";
$query = mysql_query($sql);

if(mysql_num_rows($query) > 0) {
$sql2 = "UPDATE users_info SET
username='". $_POST['username'] ."',
sexe='". $_POST['sexe'] ."',
ddn='". $_POST['ddn'] ."',
taille='". $_POST['taille'] ."'
WHERE username = '$session->username' "; // Just put your previous code here
mysql_query($sql2) or die(mysql_error());
}

else
{
$sql3 = "INSERT INTO users_info(username,sexe,ddn,taille)
values(\"$_POST['username']\",\"$_POST['sexe']\",
\"$_POST['ddn']\",\"$_POST['taille']\")";
mysql_query($sql3) or die(mysql_error());
}

header("Location: index.php?id=modif_cmpt");

?>


If it don't work it shud give you an error unless I misplaced a ' or " or something, and just can't see it.
I think it will work though, if thats what you mean..

What it does is it looks counts the # of rows in the table users_info that has the sessions username in the username field.. If there is more than 0 then it updates the table.

If there is less than one, then it just updates it.

Basically, if it works correctly, there will only be 1 entry for each user using the program.

If you have any questions just ask.
MrTouz
Dec 24 2008, 12:25 PM
There is only one thing i can say : You are a master.

It worked perfectly, it changed the users info if the user existed in the database... and it created the user if he did not exist.
I only modified your script where you put the \ (backslashes everywhere) I did not know why did you put it... i tried without first (mostly because i was lazy one adding all those backslashes. But it worked. So i guess i did not need them.

You closed on of my chapters. I thank you very much !
Jetteh22
Dec 24 2008, 04:29 PM
I don't know why I do that.
It's how I read it in my book that I read to read PHP.
If you have like 12 extra bucks, I'd really suggest this book - It's got alot of useful information and it isn't hard to follow.

PHP In Easy Steps
MrTouz
Dec 24 2008, 05:24 PM
QUOTE
PHP In Easy Steps

Is that the name of the book ?

Ill pay 50 bucks if its gonna teach me good stuff. I havent found an easy book to follow... i mean, im not stupid i can of understand different languages and kind of get php but really these books... they make it look so much harder !
Jetteh22
Dec 24 2008, 05:32 PM
Yeah, don't get the PHP For Dummies books, the hardly explain and are a waste of money.

I will find a link for you. Sorry to be short, on cell phone and driving but when I get home I'll link you. Think the na.e is PHP In Easy Steps. Thet are awesome, all the In Easy Steps books.
Jetteh22
Dec 24 2008, 10:26 PM
Okay, I'm home now.
Man, it's hard to Forum Post and drive at the same time!

Okay.. I got the link for you. Everything I know about PHP I learned from this book, and I can vouch that it is DEFINATELY wayyyy better than PHP For Dummies (I bought the for dummies book first, and -hated- it.. I saw the half-sized[and priced] in easy steps, thought it would be bad, but gave it a try and loved it). All the code is color coded and the explain things as they go along - they don't just THROW shit at you and hope you understand it.

I'm sure you can find it used on Amazon.com for cheaper, maybe.

Good luck!

EDIT: It's also good to look back in for references to certain things, things you don't use much but they mention it in the book.
swordz
Dec 24 2008, 11:34 PM
The reason the backslashes are needs are to make the " metacharacters (i.e start/stop string) appear as literal " characters. Without the \ it ends the string, then obviously appends the $_POST data string, then appends the next string from the next ". Which makes me think there's possibly some serious error handling going on, as if the string has stopped, you should need a . to concatenate.

I'd be interested in the final code, just to see what it actually is!

And I suspect it might be slightly safer if you replace \" with ' everywhere. Although I'd be happier if Alex said some more first!

swordz
Archadium
Dec 29 2008, 07:45 PM
I liked the PHP For Dummies book, but it does leave some things out. It's good if you already have some general grasp of php or programming in general. It's kinda fast-paced through the book, and sometimes the example projects don't make a whole lot of sense. But, it did help me.

I don't think there is any single definitive resource that isn't a huge and daunting task to read by itself. The best thing to do is vary your reading materials, as different authors have different perspectives on how to teach the stuff, and you may find one who "speaks" to you.
MrTouz
Jan 17 2009, 06:23 PM
QUOTE
I'd be interested in the final code, just to see what it actually is!


It was asked somewhere else, i don't remember by who. I will show up my script once done. Tho i can say i am almost done... and damn it looks almost complete.
Lance Spurgeon
Sep 17 2010, 07:47 PM
Hi Guys

I really need help this code please.

I don't have much php experience so I am a newbie. I am trying to update mysql table from an xml feed where by either inserting or update depending on if a row is in the product table or not..
Firstly my report is duplicating the header of my table on every line and secondly it isn't updating the stock fields. BTW I got a part of this code of this forum smile.gif
While I am at it what would the typical cron job for running a script like this be?

CODE
<?php
require_once('login.php');

$html = "http://xxxxxxxxxxxx/feeds.2.0/status-xml.asp";
$xml = simplexml_load_file($html);


foreach($xml->products as $product){
foreach ($product as $pro){


$sql = "SELECT * FROM `product` p JOIN `product_description` d ON p.product_id = d.product_id WHERE `p`.`model` = '".$pro->code."'";
$query = mysql_query($sql);

echo '<table width="687" border="0" cellspacing="3" cellpadding="2">';
echo '<tr>';
echo ' <td width="14%" align="center"><strong>Stock Code</strong></td>';
echo ' <td width="50%" align="center"><strong>Description</strong></td>';
echo ' <td width="12%" align="center"><strong>Stock Qty</strong></td>';
echo ' <td width="13%" align="center"><strong>Status</strong></td>';
echo '</tr>';

if(mysql_num_rows($query) > 0) {

$sql2 = "UPDATE `product` p SET `p`.`quantity` = '".$pro->stock."' WHERE `p`.`product_id` = '".$query[0]['product_id']."'";
mysql_query($sql2) or die(mysql_error());
echo '<tr>';
echo ' <td align="center">'.$pro->code;
echo '</td>';
echo ' <td align="left">'.$pro->name;
echo '</td>';
echo ' <td align="center">'.$pro->stock;
echo '</td>';
echo ' <td align="center">Updated</td>' ;
echo '</tr>';
}
else {

// $sql3 = "INSERT INTO users_info(username,sexe,ddn,taille)
// values(\"$_POST['username']\",\"$_POST['sexe']\",
// \"$_POST['ddn']\",\"$_POST['taille']\")";
// mysql_query($sql3) or die(mysql_error());
echo '<tr>';
echo ' <td align="center">'.$pro->code;
echo '</td>';
echo ' <td align="left">'.$pro->name;
echo '</td>';
echo ' <td align="center">'.$pro->stock;
echo '</td>';
echo ' <td align="center">Inserted</td>';
echo '</tr>';
}
echo '</table>';
}
}
?>


Any help will be apprecitate
Pixeltor
Oct 3 2010, 05:04 PM
wait. so what you want to do is update if row exists, and insert if not?

because I simply use this query for tracking where people are on my site.

CODE
mysql_query(" INSERT INTO useronline (id, ip, ts, url, forum) VALUES ( '".$id."', '".$ip."', '".$timestamp."', '".$url."', '".$forum."' )
ON DUPLICATE KEY UPDATE ts = '".$timestamp."', url = '".$url."', forum = '".$forum."' ");


it checks to see if the primary key of the row you want to insert matches an already existing row. If it does, it updates instead.

EDIT: oops didn't realize this thread was dead.
nfljerseys
Oct 6 2010, 02:58 AM
What it does is it looks counts the # of rows in the table users_info that has the sessions username in the username field.. If there is more than 0 then it updates the table.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2013 Invision Power Services, Inc.