[SOLVED] Need some help with PHP/MySQL Bookstore

[sstoveld]

hey guys, first off, im new here, first post.

 

ok now, i've got this bookstore im trying to get working, but ive got something wrong here.

 

the application is supposed to display a list of books with their stock and prices with a radio button next to each of them, and a submit button to submit which book they would like to buy. the info is stored in a mysql db. when a book is "purchased", it sends a query to the db to decrease the stock of that particular book by one.

 

when the stock reaches zero, the radio button should not be there anymore, not allowing the user to "purchase" another copy.

 

now thats out of the way, here's my code:

 

////////////// CHECK IF UPDATED /////////////////

if (isset($_POST['submit'])){
	$id=$_POST['id'];
	$sql = "UPDATE stock SET stock = stock - 1 WHERE id = '$id'";
	if (!mysql_query($sql)){
		echo "<p>Error occurred. ".mysql_error()."</p>";
	}else{
		echo "<p>Stock updated successfully</p>";
	}
}else{
///////// THE STOCK IS PASSED FROM THE URL ////////////	
$id=$_GET['id'];
}
////////// QUERY DATABASE //////////
$queryresult = @mysql_query('SELECT title, stock, price FROM stock LEFT JOIN books ON stock.title_id = books.id ORDER BY title;');
if (!$queryresult) {
	exit ('<p>Error in query.'. mysql_error().'</p>');
} 


////////// MAKE AN HTML TABLE TO HOLD THE DATA ////////////
?>      
    <table>
    	<tr>
        	<td><strong>Title</strong></td>
            <td><strong>Stock</strong></td>
            <td><strong>Price</strong></td>
        </tr>
<?php

/////////// RESULTS OF QUERY AND PUBLISH TO TABLE ///////////

	while ($row = mysql_fetch_array($queryresult)){
	echo '<tr><td>'.$row['title'].'</td><td>'.$row['stock'].'</td><td>'.$row['price'].'</td><td><input type="radio" name="id" value='.$row['title_id'].' /></td></tr>';
}
?> 
</table>
    
    <p>
	<form name="submit" action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post">
	<input type="submit" value="Submit" name="submit" />
	</form>
    </p> 

 

it seems that the id's are not being assigned to each radio button properly

 

can anyone give me a hand? here's a screenshot of my db:

 

 

thanks for any help in advance

[WolfRage]

Notice that below I set the name parameter on your radio input.

<?php
////////////// CHECK IF UPDATED /////////////////
if (isset($_POST['submit'])){
$id=$_POST['id'];
$sql = "UPDATE stock SET stock = stock - 1 WHERE id = '$id'";
if (!mysql_query($sql)){
echo "<p>Error occurred. ".mysql_error()."</p>";
}else{
echo "<p>Stock updated successfully</p>";
}
}else{
///////// THE STOCK IS PASSED FROM THE URL //////////// 
$id=$_GET['id'];
}
////////// QUERY DATABASE //////////
$queryresult = @mysql_query('SELECT title, stock, price FROM stock LEFT JOIN books ON stock.title_id = books.id ORDER BY title;');
if (!$queryresult) {
exit ('<p>Error in query.'. mysql_error().'</p>');
} 
////////// MAKE AN HTML TABLE TO HOLD THE DATA ////////////
?>      
    <table>
    <tr>
        <td><strong>Title</strong></td>
            <td><strong>Stock</strong></td>
            <td><strong>Price</strong></td>
        </tr>
<?php
/////////// RESULTS OF QUERY AND PUBLISH TO TABLE ///////////
while ($row = mysql_fetch_array($queryresult)){
/*Notice the change I made on this line below where I set the name parameter.*/
echo '<tr><td>'.$row['title'].'</td><td>'.$row['stock'].'</td><td>'.$row['price'].'</td><td><input type="radio" name="'.$row['title'].'" value='.$row['title_id'].' /></td></tr>';
}
?> 
</table>
    <p>
<form name="submit" action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post">
<input type="submit" value="Submit" name="submit" />
</form>
    </p> 

[sstoveld]

Notice that below I set the name parameter on your radio input.

<?php
////////////// CHECK IF UPDATED /////////////////
if (isset($_POST['submit'])){
$id=$_POST['id'];
$sql = "UPDATE stock SET stock = stock - 1 WHERE id = '$id'";
if (!mysql_query($sql)){
echo "<p>Error occurred. ".mysql_error()."</p>";
}else{
echo "<p>Stock updated successfully</p>";
}
}else{
///////// THE STOCK IS PASSED FROM THE URL //////////// 
$id=$_GET['id'];
}
////////// QUERY DATABASE //////////
$queryresult = @mysql_query('SELECT title, stock, price FROM stock LEFT JOIN books ON stock.title_id = books.id ORDER BY title;');
if (!$queryresult) {
exit ('<p>Error in query.'. mysql_error().'</p>');
} 
////////// MAKE AN HTML TABLE TO HOLD THE DATA ////////////
?>      
    <table>
    <tr>
        <td><strong>Title</strong></td>
            <td><strong>Stock</strong></td>
            <td><strong>Price</strong></td>
        </tr>
<?php
/////////// RESULTS OF QUERY AND PUBLISH TO TABLE ///////////
while ($row = mysql_fetch_array($queryresult)){
/*Notice the change I made on this line below where I set the name parameter.*/
echo '<tr><td>'.$row['title'].'</td><td>'.$row['stock'].'</td><td>'.$row['price'].'</td><td><input type="radio" name="'.$row['title'].'" value='.$row['title_id'].' /></td></tr>';
}
?> 
</table>
    <p>
<form name="submit" action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post">
<input type="submit" value="Submit" name="submit" />
</form>
    </p> 

 

thanks so much for the quick reply! i noticed the change you made with the "" around the name. i applied that to the value, but it still doesnt seem to work for me... did i do something wrong here?

 

here's the code:

 

while ($row = mysql_fetch_array($queryresult)){
	echo '<tr><td>'.$row['title'].'</td><td>'.$row['stock'].'</td><td>'.$row['price'].'</td><td><input type="radio" name="'.$row['title'].'" value="'.$row['id'].'" /></td></tr>';
}

 

thanks, i appreciate the help

[FaT3oYCG]

remove the /> before the last </td>

[WolfRage]

Well it had to have worked, you stated the ID aka the name was not being assigned now it is assigned. What are you really trying for that is not working? Are you getting any errors and can you be more specific?

[sstoveld]

well when i go to view source and look at where the table is, it shows each name of the book for what you  fixed in my code. example:

 

<tr><td>A Beautiful Mind</td><td>13</td><td>12.99</td><td><input type="radio" name="A Beautiful Mind" value=""</td></tr>

 

see how it shows A Beautiful Mind. thats from what you fixed in my code:

 

name="'.$row['title'].'"

 

but for the radio button, the value is not there. viewing the source from firefox:

 

<tr><td>A Beautiful Mind</td><td>13</td><td>12.99</td><td><input type="radio" name="A Beautiful Mind" [b]value=""[/b]</td></tr>

 

see that there is no value for the radio button there. i cant seem to get it to assign the id there.

 

and after i removed the  /> before that last </td> it seems that the radio button tag is not closed

[WolfRage]

Can you echo $row['id']; ? What do you get?

[FaT3oYCG]

oh sorry missed that one, make sure you are using the right case aswell for that statment i.e. id wont work if the item in the database is stored as ID or Id

[sstoveld]

Can you echo $row['id']; ? What do you get?

 

sorry, i am very new to this. how do i do that?  :-\

[sstoveld]

oh sorry missed that one, make sure you are using the right case aswell for that statment i.e. id wont work if the item in the database is stored as ID or Id

 

yup, it's id. thanks

[WolfRage]

<?php 
echo '<tr><td>'.$row['title'].'</td><td>'.$row['stock'].'</td><td>'.$row['price'].'</td><td><?php echo 'This is a test! '.$row['id'].' <-Did you see anything?'; ?><input type="radio" name="'.$row['title'].'" value='.$row['title_id'].' /></td></tr>';
?>

[sstoveld]

<?php 
echo '<tr><td>'.$row['title'].'</td><td>'.$row['stock'].'</td><td>'.$row['price'].'</td><td><?php echo 'This is a test! '.$row['id'].' <-Did you see anything?'; ?><input type="radio" name="'.$row['title'].'" value='.$row['title_id'].' /></td></tr>';
?>

 

lol im probably being a retard here but i get a parse error with that code  :-\

[Philip]

Erm...

 

You have

<?php echo 'This is a test! '.$row['id'].' <-Did you see anything?; ?>

Inside your echo statement

 

should be:

echo '<tr><td>'.$row['title'].'</td><td>'.$row['stock'].'</td><td>'.$row['price'].'</td><td>This is a test! '.$row['id'].'...

 

 

[WolfRage]

Ok so I was a little retarded on that one, I am chatting and working and two jobs at the same time, well trying to help out. I'll take a step back now...

It also comes from the way I am used to coding, I never echo html, unless I have too. I perfer to break in and out of php as needed.

[sstoveld]

Ok so I was a little retarded on that one, I am chatting and working and two jobs at the same time, well trying to help out. I'll take a step back now...

It also comes from the way I am used to coding, I never echo html, unless I have too. I perfer to break in and out of php as needed.

 

haha no worries

 

ok well, im not sure what im supposed to get... but with this code:

 

<?php

/////////// RESULTS OF QUERY AND PUBLISH TO TABLE ///////////

	while ($row = mysql_fetch_array($queryresult)){
		echo '<tr><td>'.$row['title'].'</td><td>'.$row['stock'].'</td><td>'.$row['price'].'</td><td>This is a test! '.$row['id'].' <-Did you see anything?; ?><input type="radio" name="'.$row['title'].'" value='.$row['title_id'].' /></td></tr>';
}
?> 

 

i get this:

 

 

edit: here's a pastebin of my whole page source:

 

http://pastebin.com/m4e72e4b2

[WolfRage]

I can't see that? "this"= X

[FaT3oYCG]

XD XD XD XD XD XD XD XD

 

change the databse query to this

 

$queryresult = @mysql_query('SELECT id, title, stock, price FROM stock LEFT JOIN books ON stock.title_id = books.id ORDER BY title;');

 

then come back and thank me

[sstoveld]

XD XD XD XD XD XD XD XD

 

change the databse query to this

 

$queryresult = @mysql_query('SELECT id, title, stock, price FROM stock LEFT JOIN books ON stock.title_id = books.id ORDER BY title;');

 

then come back and thank me

 

says:

Error in query.Column 'id' in field list is ambiguous

 

id is a column in the stock table. why would it say this?

[FaT3oYCG]

erm wow now something that should have been simple just wasnt, that should have solved the problem as you need to select the id from the table to be able to access it with the array i.e. $row['id'] but you got some wierd error

 

all i can think to do is change the feild name to stock_id in your database and code and try that and if it works ill explain in a min why

[Philip]

SELECT id, title, stock, price FROM stock LEFT JOIN books ON stock.title_id = books.id ORDER BY title

SELECT books.id as id, title, stock, price FROM stock LEFT JOIN books ON stock.title_id = books.id ORDER BY title

[sstoveld]

erm wow now something that should have been simple just wasnt, that should have solved the problem as you need to select the id from the table to be able to access it with the array i.e. $row['id'] but you got some wierd error

 

all i can think to do is change the feild name to stock_id in your database and code and try that and if it works ill explain in a min why

 

awesome! it shows the value for each radio button now in the source. i changed id to stock_id everywhere.

 

but i still have a problem. when i select a radio button and hit submit, it isnt decreasing the stock. whats going on?

 

edit: here's a new pastebin: http://pastebin.com/m33ae0d09

[FaT3oYCG]

cool its coz the book table has id as one of its fields aswell, from now on name then with the table prefix e.g. book_id and if you can go through and change all the others you have

 

try changing this

$sql = "UPDATE stock SET stock = stock - 1 WHERE stock_id = '$stock_id'";

 

to

 

$sql = "UPDATE stock SET stock = stock - 1 WHERE stock_id = '$id'";

[WolfRage]

That's some great team work!

[sstoveld]

cool its coz the book table has id as one of its fields aswell, from now on name then with the table prefix e.g. book_id and if you can go through and change all the others you have

 

try changing this

$sql = "UPDATE stock SET stock = stock - 1 WHERE stock_id = '$stock_id'";

 

to

 

$sql = "UPDATE stock SET stock = stock - 1 WHERE stock_id = '$id'";

 

hmm no go stock still wont change

[sstoveld]

That's some great team work!

lol