PHP MYSQL Question

[chrishawkins]

I am trying to put together a script that will show me a report of won auctions that the deposit has been paid on. However, there are a few tweaks that I was hoping to get some help with.

1. I would like to be able to select from both the _users tables as well as the _winners table and where the user_id and buyer_id are the same, it will show either the name or the username for that buyer_id from the _users table.

2. When it finds that the payment_status is confirmed and after it has been displayed to me, I need it to update the field named printed that I have added in the _winners table to a value I set (i.e. printed), so the next time I run the script it will not show the rows when the column printed is not blank.

This is what I have so far;

<?php
$con = mysql_connect("localhost","username","pswd");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM _winners
WHERE payment_status='confirmed' AND printed='' AND buyer_id BETWEEN 2 AND 1000000000 ORDER BY bid_amount DESC");

echo "<table border='5'>
<tr>
<th>Auction ID</th>
<th>Bid Amount</th>
<th>Deposit Status</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['auction_id'] . "</td>";
echo "<td>" . $row['bid_amount'] . "</td>";
echo "<td>" . $row['payment_status'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>

Let me know if you can help me out with this.

Thanks in advance,

Chris

[Tommybs]

for part 1 you can use an inner join

$result = mysql_query("SEELCT * from _winners AS w INNER JOIN _users AS u on u.user_id = w.buyer_id");

As for the update. Are you saying that after you have echoed the row you want to then update it so it never shows again when someone accesses your page a 2nd time?

If so then in your while loop you would need another query at the end of it to update.

$q2 = @mysql_query("UPDATE _winner SET printed = 'printed' WHERE auction_id = ".mysql_real_escape_string($r['auction_id'])."");

I'm not sure if there would be a better way to do this.
As you loop through you could possibly store all the auction_ids in a comma delimited string and then use an mysql 'IN' clause to update all rows at once.

e.g.

$string_ids = "";
while($row = mysql_fetch_array($result)) 
{ 
$string_ids .= $row['auction_id'].",";
echo "<tr>"; 
echo "<td>" . $row['auction_id'] . "</td>"; 
echo "<td>" . $row['bid_amount'] . "</td>"; 
echo "<td>" . $row['payment_status'] . "</td>"; 
echo "</tr>"; 
} 
$string_ids = substr_replace($string_ids ,"",-1); //remove last comma
$q = @mysql_query("UPDATE _winner SET printed = 'printed' WHERE auction_id IN ($string_ids)");

Obviously test this first as it's something I've only just knocked up.

[chrishawkins]

Thank you for your response, I was actually able to put it together after hours of research last night. However, there are a couple of things that I am still trying to do that I was hoping you might be able to help me out with.

After the script posted below, please read on to see what else I am trying to do with it.

This is the script I am using, and it is working just as I need it to;

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT *, probid_users.name AS name,
probid_winners.winner_id AS winnerid
FROM probid_winners
left join probid_users ON probid_users.user_id = probid_winners.buyer_id
WHERE probid_winners.payment_status='confirmed' AND printed='' AND buyer_id BETWEEN 2 AND 1000000000 ORDER BY bid_amount DESC");

echo "<table border='5'>
<tr>
<th>Name</th>
<th>Auction ID</th>
<th>Bid Amount</th>
<th>Deposit Status</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['auction_id'] . "</td>";
echo "<td>" . $row['bid_amount'] . "</td>";
echo "<td>" . $row['payment_status'] . "</td>";
echo "</tr>";
$res = mysql_query("UPDATE probid_winners SET printed='printed' WHERE probid_winners.winner_id = " . $row["winnerid"]);

}
echo "</table>";

mysql_close($con);
?>

However, even though the script works as needed., I was still hoping to be able to add three buttons to it.

1st button; I would like to be able to have a button to press to print the page.

2nd button; I would like to be able to have a button that will update the printed tables only after I press it. This way, there won't be a chance that I would accidentally refresh the page and have the script automatically update the printed column.

3rd button; I would like to have a button that will actually process the script. This way, the script is not precessing until I am ready and all of the information is not there the moment I get to the page. I guess that I could manage to so this by a button on an index page that redirects me to the page with the script, but I was hoping to see what I could do about having the process button just be directly on the same page.

Let me know if all of this is possible, and if you wouldn't mind, it would be great if you could give me a working mock-up.

Thanks again, in advance, for all of the help. You guys here are great!

[Tommybs]

1) For the print page button I think it's slightly more complicated. You can use javascript to allow users to print the page, but you might also want to provide another version of the page with a stylesheet purely for printing. I think there's a lot more to this as a user could print the page as is via their web browser.

2) For the update button you have 2 options. You can either have another form next to each row that submits, or you could merely use $_GET parameters. The issue with the get parameters is that it would only take someone going to a url to update the status. For creating another form it would look similar to the following:

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['auction_id'] . "</td>";
echo "<td>" . $row['bid_amount'] . "</td>";
echo "<td>" . $row['payment_status'] . "</td>";
echo "<td>". <form name=\"update_row\" method=\"post\" action=\"page.php?id=".$row['winnerid']."\">
<input type=\"hidden\" name=\"updating_row\" value=\"".$row['winnerid']."\" />
<input type=\"submit\" name=\"submit_update\" value=\"Remove\" />
</form>
echo "</tr>";
if(isset($_POST['updating_row'])){
$res = mysql_query("UPDATE probid_winners SET printed='printed' WHERE probid_winners.winner_id = " . mysql_real_escape_string($_POST['winnerid']));
}
} 

3) Again here you could use $_GET or $_POST parameters. So if you have a form at the top of the page with a value like "show_records" you just check if this is set. Just wrap an if statement looking for if this is set around the code that shows the results. If it's not the results wont show. I've tried not to write this part out for you as it is reasonable simple. Obviously I hope you are trying to understand what I've written instead of just copying and pasting. Any questions about what's going on then just reply here and I'm sure someone will reply.

[Tommybs]

sorry

if(isset($_POST['updating_row'])){
$res = mysql_query("UPDATE probid_winners SET printed='printed' WHERE probid_winners.winner_id = " . mysql_real_escape_string($_POST['winnerid']));
} 

should be

if(isset($_POST['updating_row'])){
$res = mysql_query("UPDATE probid_winners SET printed='printed' WHERE probid_winners.winner_id = " . mysql_real_escape_string($_POST['updating_row']));
} 

[chrishawkins]

I will definitely be able to do without the print button, it was just an option if found to be easy to implement. As I am still doing my best to learn as much as possible, I am sure it will take a little bit more time than it would you for me to even implement what you've written here for me, which I am VERY appreciative of.

Anymore instructions you can supply, even if you have to talk to me as if I am a child, is VERY much appreciated.

Thanks again,

Chris

[Tommybs]

As I say no problem, we were all beginners once. Any questions about the code just ask and I'll do my best to explain. Don't worry about anything I'm still learning myself and there are some very helpful people on here.

[chrishawkins]

I'm stuck, and this is more than likely ALL wrong!

<form name="form1" method="post" action="">
<label>
<input type="submit" name="update" id="update" value="Update Records">
</label>
<form name="form2" method="get" action="">
<label>
<input type="submit" name="show_records" id="show_records" value="Show Records">
</label>
</form>
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my)db", $con);

if(isset($_GET['show_results'])){
$res = mysql_query("SELECT *, probid_users.name AS name,
probid_winners.winner_id AS winnerid
FROM probid_winners
left join probid_users ON probid_users.user_id = probid_winners.buyer_id
WHERE probid_winners.payment_status='confirmed' AND printed='' AND buyer_id BETWEEN 2 AND 1000000000 ORDER BY bid_amount DESC = " . mysql_real_escape_string($_GET['show_results']));
}

echo "<table border='5'>
<tr>
<th>Name</th>
<th>Auction ID</th>
<th>Bid Amount</th>
<th>Deposit Status</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['auction_id'] . "</td>";
echo "<td>" . $row['bid_amount'] . "</td>";
echo "<td>" . $row['payment_status'] . "</td>";
echo "<td>". <form1=\"update_row\" method=\"post\" action=\"page.php?id=".$row['winnerid']."\">
<input type=\"hidden\" name=\"updating_row\" value=\"".$row['winnerid']."\" />
<input type=\"submit\" name=\"submit_update\" value=\"Remove\" />
</form>
echo "</tr>";

if(isset($_POST['updating_row'])){
$res = mysql_query("UPDATE probid_winners SET printed='printed' WHERE probid_winners.winner_id = " . mysql_real_escape_string($_POST['updating_row']));
}
}
echo "</table>";

mysql_close($con);
?>

[Tommybs]

Your first form "form1" is missing a closing tag </form>

Also the $_GET parameter you're looking for is show_results
whereas your field is called show_records so that needs changes. Try changing those for now and then I'll help you with other changes

[Tommybs]

I'd also set an action on the forms. So for the $_GET form set the action to page.php?show_record=1.

Another things I've just noticed is that in your while loop you are saying :
while($row = mysql_fetch_array($result))
$result doesn't exist in the code you posted as you used $res. You then use $res again for the next query. I'd change this to something else such as $res_2 so there's no chance of it interfering with the first query

[chrishawkins]

I have updated the following that you have mentioned to;

<form name="form1" method="post" action="">
<label>
<input type="submit" name="update" id="update" value="Update Records">
</label>
</form>
<form name="form2" method="get" action="test.php?show_record=1">
<label>
<input type="submit" name="show_results" id="show_results" value="Show Results">
</label>
</form>
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my)db", $con);

if(isset($_GET['show_results'])){
$res = mysql_query("SELECT *, probid_users.name AS name,
probid_winners.winner_id AS winnerid
FROM probid_winners
left join probid_users ON probid_users.user_id = probid_winners.buyer_id
WHERE probid_winners.payment_status='confirmed' AND printed='' AND buyer_id BETWEEN 2 AND 1000000000 ORDER BY bid_amount DESC = " . mysql_real_escape_string($_GET['show_results']));
}

echo "<table border='5'>
<tr>
<th>Name</th>
<th>Auction ID</th>
<th>Bid Amount</th>
<th>Deposit Status</th>
</tr>";

while($row = mysql_fetch_array($res_2))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['auction_id'] . "</td>";
echo "<td>" . $row['bid_amount'] . "</td>";
echo "<td>" . $row['payment_status'] . "</td>";
echo "<td>". <form1=\"update_row\" method=\"post\" action=\"test.php?id=".$row['winnerid']."\">
<input type=\"hidden\" name=\"updating_row\" value=\"".$row['winnerid']."\" />
<input type=\"submit\" name=\"submit_update\" value=\"Remove\" />
</form>
echo "</tr>";

if(isset($_POST['updating_row'])){
$res = mysql_query("UPDATE probid_winners SET printed='printed' WHERE probid_winners.winner_id = " . mysql_real_escape_string($_POST['updating_row']));
}
}
echo "</table>";

mysql_close($con);
?>

Now, I am getting an error of;

Parse error: syntax error, unexpected '<' in C:\VertrigoServ\www\test\test.php on line 43

[Tommybs]

echo "<td>". <form1=\"update_row\" method=\"post\" action=\"test.php?id=".$row['winnerid']."\">
<input type=\"hidden\" name=\"updating_row\" value=\"".$row['winnerid']."\" />
<input type=\"submit\" name=\"submit_update\" value=\"Remove\" />
</form>

this line here is missing a "; at the end ( that includes the double quote by the way)

[chrishawkins]

Would you mind chatting via skype ( chris.m.hawkins ) or logging in via my logmein account to remotely access my desktop by any chance?

[Tommybs]

Not on skype I'm afraid. I've noticed another error, even with the above line. Try replacing the code with this:
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my)db", $con);

if(isset($_GET['show_results'])){
$res = mysql_query("SELECT *, probid_users.name AS name,
probid_winners.winner_id AS winnerid
FROM probid_winners
left join probid_users ON probid_users.user_id = probid_winners.buyer_id
WHERE probid_winners.payment_status='confirmed' AND printed='' AND buyer_id BETWEEN 2 AND 1000000000 ORDER BY bid_amount DESC = " . mysql_real_escape_string($_GET['show_results']));
}

echo "<table border='5'>
<tr>
<th>Name</th>
<th>Auction ID</th>
<th>Bid Amount</th>
<th>Deposit Status</th>
</tr>";

while($row = mysql_fetch_array($res_2))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['auction_id'] . "</td>";
echo "<td>" . $row['bid_amount'] . "</td>";
echo "<td>" . $row['payment_status'] . "</td>";
echo "<td><form1=\"update_row\" method=\"post\" action=\"test.php?id=".$row['winnerid']."\">
<input type=\"hidden\" name=\"updating_row\" value=\"".$row['winnerid']."\" />
<input type=\"submit\" name=\"submit_update\" value=\"Remove\" />
</form>";
echo "</tr>";

if(isset($_POST['updating_row'])){
$res = mysql_query("UPDATE probid_winners SET printed='printed' WHERE probid_winners.winner_id = " . mysql_real_escape_string($_POST['updating_row']));
}
}
echo "</table>";

mysql_close($con);
?>

[chrishawkins]

Great, that worked and everything seems to be displaying, but I don't want to test the show results until I pass this error by you;

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\VertrigoServ\www\test\test.php on line 37

Which would be this line;
while($row = mysql_fetch_array($res_2))

[edited by: chrishawkins at 9:25 pm (utc) on Nov. 30, 2009]

[Tommybs]

What query does that relate to?

if its

if(isset($_GET['show_results'])){
$res = mysql_query("SELECT *, probid_users.name AS name,
probid_winners.winner_id AS winnerid
FROM probid_winners
left join probid_users ON probid_users.user_id = probid_winners.buyer_id
WHERE probid_winners.payment_status='confirmed' AND printed='' AND buyer_id BETWEEN 2 AND 1000000000 ORDER BY bid_amount DESC = " . mysql_real_escape_string($_GET['show_results']));
} 

change it to:

if(isset($_GET['show_results'])){
$res = mysql_query("SELECT *, probid_users.name AS name,
probid_winners.winner_id AS winnerid
FROM probid_winners
inner join probid_users ON probid_users.user_id = probid_winners.buyer_id
WHERE probid_winners.payment_status='confirmed' AND printed='' 
ORDER BY bid_amount DESC");
} 

[Tommybs]

Also I'd add more to the isset($_GET['show_results'])

try changing it to:

if(isset($_GET['show_results']) && $_GET['show_results'] == 1){

Otherwise someone could pass anything in the show_results string and it would show

[chrishawkins]

Are you saying that I need to remove this line altogether;

mysql_real_escape_string($_GET['show_results']));

[chrishawkins]

No matter what, I am still getting;

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\VertrigoServ\www\test\test.php on line 37

[Tommybs]

yeah. DESC = is not valid as far as I'm aware and it's not part of the clause, and I don't see why you need that there at all

[chrishawkins]

Any other messenger that you prefer? The option is still open to you connecting via my logmein account

[chrishawkins]

I only used DESC = because I'm really weird when it comes to having things organized by numerical value. If you'd like to directly connect, I'll show you everything I'm doing.

[Tommybs]

msn messenger? pm me your details

[Tommybs]

I'm logging off now and I won't actually be around for the next few days ( maybe a bit on Wed night and I might be able to login for a tiny bit tomorrow ) but not as much help as I've tried to offer tonight.

What I would suggest doing is working in phpmyadmin and typing in your mysql queries into there testing them with variables that you know will work. It should help you debug the problems. I'm presuming it is the main query that is causing issues.

Having written all that out i think it's because your while loop is refrencing the wrong query.

while($row = mysql_fetch_array($res_2)) 

should be

while($row = mysql_fetch_array($res)) 

$res_2 hasn't been defined at this point. it should be $res

[chrishawkins]

I'll update here with the solution I've found that works.

Thanks again for all of the help,