Dynamic Table Question with INSERT into and UPDATE

[Usarjjaco]

Hey guys,Thought I'd drop in to get a group opinion. Here's what I'm trying to do:When user picks up item A they get item A store in a table called "Items" under their username. ... Simple enough.But my question is this; if my tables are set up with these columns:username | item | location1 | location2 | location3 |- Step 1: How would I perform it so that the first time they pick up an item it creates a new row with their username and then the item name and places a quantity of x underneath location 1 (I know that this part can be done with a INSERT INTO)- Step 2: This is where I'm a little hazy. Lets say user A already has the item "Baseball"... Therefore there is a row for him with that item already... how can I let the script check to see if he has the item before deciding whether to either do a INSERT INTO and create new row or a UPDATE to update the quantity of that row?Thanks guys,JJ

[justsomeguy]

Just do a select to get the row and see if it returns anything. If it does, update, if not, insert.

[Usarjjaco]

Just do a select to get the row and see if it returns anything. If it does, update, if not, insert.

Ok so basically just $variable=mysql_query(SELECT * FROM items WHERE username='{$username} AND item='{$item}'If ($variable=="") <--- (I think that's how you ask if a variable returns anything?){Use insert into statement here}else{update statement here}That look right?

[justsomeguy]

You can use mysql_num_rows to check how many rows a query returned, mysql_query will never return an empty string.

[Usarjjaco]

You can use mysql_num_rows to check how many rows a query returned, mysql_query will never return an empty string.

Ok; so replace the query with the num_rows; and besides that does my script overview look like it would do the necessary task?$variable1=mysql_num_rows("SELECT * FROM items WHERE username='{$uname}'")something like if($variable<1)

[ShadowMage]

Ok; so replace the query with the num_rows; and besides that does my script overview look like it would do the necessary task?$variable1=mysql_num_rows("SELECT * FROM items WHERE username='{$uname}'")something like if($variable<1)

Not quite. You need to use mysql_num_rows on the result of the query. ex:

$variable=mysql_query(SELECT * FROM items WHERE username='{$username} AND item='{$item}'$numRows = mysql_num_rows($variable);if ($numRows < 1) {   //do something}

[Usarjjaco]

Not quite. You need to use mysql_num_rows on the result of the query. ex:

$variable=mysql_query(SELECT * FROM items WHERE username='{$username} AND item='{$item}'$numRows = mysql_num_rows($variable);if ($numRows < 1) {   //do something}

Awesome; thanks mate.

[bloodyjoe]

Hi i have a problem relevant to yours, want to insert one or plus one for every time someone looks at an article, but it just gives a mysql_num_rows error, it gets the variable from the other site ands so on. But as soon as it reaches the mysql_num_rows it go´s wrong, anyone got an idea, would be very thankfull.$side = $_get['side'];$side_plus = ++;$side_plus_en = 1;if($_get['side']){ $query = mysql_query("select sertal from kategorier where id='$side'"); $numrows = mysql_num_rows($query); if($numrows < 1){ $insert = insert into article ('sertal') values ($side_plus_en); and so on }else{ update sertal with $side_plus }}

[justsomeguy]

If the error is that it expected a MySQL result resource, then the query failed. You can check to see if mysql_query returned false and, if so, use mysql_error to get the error message.

[bloodyjoe]

well i got this far now, it seems to work with insertin one but if it already has one in the database the it will not update it with one ekstra, you know whts wrong mate$kategori_id = $_GET['kategori_id']; $indlaeg_id = $_GET['indlaeg_id']; $sertal_plus =+1; $sertal_plus_en = 1; if ($_GET['indlaeg_id']){ $query = "SELECT sertal FROM kategorier WHERE id=$indlaeg_id"; $res_sertal = mysql_query($query) or die(mysql_error()); $row_sertal = mysql_fetch_array($res_sertal); if ($row_sertal['sertal'] < 1){ $insert = "INSERT INTO kategori WHERE id=$indlaeg_id (sertal) VALUES ($sertal_plus_en)"; mysql_query($insert) or die(mysql_error()); }elseif ($row_sertal['sertal'] >= 1){ $update ="UPDATE kategorier SET sertal='{$sertal_plus}' WHERE id= $indlaeg_id"; mysql_query($update)or die(mysql_error()); } }

[justsomeguy]

Doing this:$sertal_plus =+1;means this:$sertal_plus = (+1);So $sertal_plus is set to 1. When you update it, you're always setting it to 1, not one more than what is already there. You can just do this to add 1:UPDATE kategorier SET sertal=sertal + 1

[bloodyjoe]

Doing this:$sertal_plus =+1;means this:$sertal_plus = (+1);So $sertal_plus is set to 1. When you update it, you're always setting it to 1, not one more than what is already there. You can just do this to add 1:UPDATE kategorier SET sertal=sertal + 1

ah, smart, makes sence to do it in the query, i got the update to work on my project by saying $sertal_old = $row['sertal'];$sertal = +1 $one_plus_the_old = $sertal + $sertal_old;and update with $one_plus_the_oldbut it would be smarter with the +1 in query.thanks mate