[RESOLVED] insert rows except for some

Desbrina

Sorry if this is more for sql, wasn't sure

Currently i have this piece of code

		if(isset($_POST['Change'])) {
			// Update user_inventory
			$sql = "UPDATE user_inventory
			SET status = '".$status."'
			WHERE deck = '".$_SESSION['deck']."'";
			$result = mysql_query($sql, $con);
			// Check to see if they set the deck to collecting
			if($status == 'collecting') {
			        // Find out from the cards table what the total amount of cards is
				$c_amount = "SELECT amount 
						FROM cards
						WHERE deck = '".$_SESSION['deck']."'
						LIMIT 1";
				$c_query = mysql_query($c_amount);
			        // Find out from user_inventory the current cards
				while ($row = mysql_fetch_array($c_query)) {
					$i_item = "SELECT card_id
								FROM user_inventory
								WHERE deck = '".$_SESSION['deck']."'";
					$i_query = mysql_query($i_item);
					while ($row = mysql_fetch_array($i_query)) {
						echo $row['card_id']."<br />Changed sucessfully<br />";
					}
				}
			}
		}

What it does is update a deck setting it to what your chose

Then if you choose to set the deck to collecting, it gets the total amount of cards from the cards table,.
Then it gets the ids of the cards you currently have (card_id / card_id is related to ID in the cards table)

I'm wondering how i then add the rest of the cards from the cards table to the user_inventory tables

The data i need to get from the cards table and put into the user_inventory table is these fields: url, alttext, deck, worth

AllSpiritsEve

First thing I would do, is make sure you are cleaning your input data before using it in an sql query, to avoid sql injection hacks. I'm not an expert on the subject, but I know it has to be done. Maybe someone else on this forum could help you, or you could search for tips on how to protect your queries.

As far as the query goes: Maybe you could do a join on your cards table and your user inventory. Using phpmyadmin, first write the join to include all cards in a deck, and only include user_inventory data if that user has a card in the deck. This would be handled with a left join, where the table in the select statement is cards and the table in the join statement is user_inventory.

I'm not writing out sql for you, because you can do it yourself and check for errors in phpmyadmin, and because you're not going to learn if you just use someone else's sql without knowing exactly what it does.

The next step, once you have that working correctly, is to add a line to the previous query where user_id = NULL. This will leave out any cards that the user has in the inventory. Now, put this code in your php file, and execute it, using print_r to display results. If it works, now you can loop through these results and add them to the user_inventory.

Desbrina

The input is checked, but its not sown on there

I wasn't asking for the sql to be written, just somewhere to start with it

Desbrina

I have this so far, not sure if its right, as its bringing up 60 rows, even though theres 20 in cards, and 3 in user_inventory, should it do this?

SELECT *
FROM cards
LEFT OUTER JOIN user_inventory
ON cards.deck = user_inventory.deck
WHERE cards.deck = 'quigon';

if i add the AND user_inventory.user_id = NULL i dont get any results. I'm not if what i've done is correct

AllSpiritsEve

Desbrina wrote:
I wasn't asking for the sql to be written, just somewhere to start with it

I apologize, I didn't mean to sound condescending.

Desbrina

Its ok, i know i learn better when doing it myself. I just had no idea where to start with it which is why i asked.

About what i have so far, is it right as it doesn't seem to be doing anything

AllSpiritsEve

Desbrina wrote:
if i add the AND user_inventory.user_id = NULL i dont get any results. I'm not if what i've done is correct

Hmm... I think I was wrong before. Try this:

SELECT * FROM cards WHERE deck = 'quigon' AND card_id NOT IN (SELECT card FROM user_inventory WHERE user_id = '1')

Desbrina

thanks, thats worked perfectly