[SOLVED] Method to Pull Specific Data from MySQL Database Not Working

[Viper114]

OK, so picture this. We currently have a back-end page that brings up all of our orders on our MySQL database made to our site. The problem is, bringing up ALL orders takes a while for the page to load, so I've been asked to come up with a way to make it load faster by truncating old orders by the order date, referenced in the MySQL database as "odate".My plan is to employ a text field that you can put in whatever date you want and a submit button that will run a MySQL query to select all orders by the date listed in the text box. Sounds simple, right? Well, so did I, except what I've come up with so far is stuck fetching all orders again, making everything I've done so far pretty much worthless. It doesn't seem to read the date properly, and I need help figuring out why.Here's the coding so far:

<form action="cart_orders_show.php" method="get">View All Orders by Shown Date:<input type="text" name="odate" value="2010-01-01 00:00:00"><input name="btn" value="Go" type="submit"></form><Table width="95%" border="1"><TR><TD><TABLE width="100%" border="0" align="center"><TR><TD>Order</TD><TD width="15%">Customer</TD><TD>Subtotal</TD><TD>Order Date</TD><TD>Fulfillment Date</TD><TD width="15%">Shipper Code</TD><TD>Status</TD></TD></TR><?phpif ($btn == "Go"){ $resultID=mysql_query("select * from cart_order where odate >= '$odate' order by oid desc ",$linkID);}if (empty($status)){ $resultID=mysql_query("select * from cart_order where order_status IS NULL order by oid desc ",$linkID);}else{$resultID=mysql_query("select * from cart_order order by oid desc ",$linkID);}for($x=1; $x< mysql_num_rows($resultID)+1; $x++){ $row = mysql_fetch_assoc($resultID); print"<TR> <TD><A href=\"cart_orders_detail.php?oid=$row[oid]\">$row[oid]</A></TD> <TD><A href=\"cart_orders_detail.php?oid=$row[oid]\">$row[ccname]</A></TD> <TD>$row[subtotal]</TD> <TD>$row[odate]</TD> <TD>$row[fdate]</TD> <TD>$row[shippercode]</TD> <TD>$row[order_status]</TD> <TD>".markhelp($row[oid])."</TD> </TR>"; }?></TABLE>

The first line is the Text Box and the Submit button I just recently created at the top of a table that neatly sorts the data pulled from the MySQL database. The PHP function that starts with if ($btn == "Go") is the coding I just made up, while everything else below that is the previous coding that gets all data. As I said, right now it doesn't matter what date I put in the newly formed text box, it still just pulls all data from the database, which I don't want it to.What could I do to change this so it truncates the orders properly? Is my PHP off somewhere? If so, how should I fix it?

[justsomeguy]

What is the value of $odate? Print out the query you're sending to MySQL so you can see what it looks like. What type of field is odate in the database?

[jeffman]

A serious logic problem here:if ($btn == "Go"){$resultID=mysql_query("select * from cart_order where odate >= '$odate' order by oid desc ",$linkID);}if (empty($status)){$resultID=mysql_query("select * from cart_order where order_status IS NULL order by oid desc ",$linkID);}else{$resultID=mysql_query("select * from cart_order order by oid desc ",$linkID);}It does not matter if $btn=="Go"; the next conditional ensures that either the second or third query will overwrite the value of $resultID. If $status is not empty, the third query will be executed, and that is the one that returns all rows.

[Viper114]

Oops, forgot that. odate is in the form of YYYY-MM-DD HH:MM:SS, so for example 2010-11-15 11:30:00. Which in this case means the field type in the database is DATETIME. I matched that for the default value given in the new text box I made.

[Viper114]

A serious logic problem here:if ($btn == "Go"){$resultID=mysql_query("select * from cart_order where odate >= '$odate' order by oid desc ",$linkID);}if (empty($status)){$resultID=mysql_query("select * from cart_order where order_status IS NULL order by oid desc ",$linkID);}else{$resultID=mysql_query("select * from cart_order order by oid desc ",$linkID);}It does not matter if $btn=="Go"; the next conditional ensures that either the second or third query will overwrite the value of $resultID. If $status is not empty, the third query will be executed, and that is the one that returns all rows.

Hmm, I'm seeing that now. As I said, the first one is the one I just made. The last two are ones that already exist that pull the data as soon as you first go to this page. While I don't want to remove that part as sometimes the people working the orders may need to go back to old orders, I want my button to override these existing queries when used. I just need to figure out how to set it all up in this case.What if I were to cut out the last query, and set it up as such:

if ($btn == "Go"){$resultID=mysql_query("select * from cart_order where odate >= '$odate' order by oid desc ",$linkID);}elseif (empty($status)){$resultID=mysql_query("select * from cart_order where order_status IS NULL order by oid desc ",$linkID);}

If done this way, it should try and follow the request given by the button, otherwise it should just pull everything (and since first opening the page doesn't do anything with the button, it should follow the second query). Right?

[jeffman]

This is certainly better. I was thinking of suggesting that you wrap the second query in an if-structure. Is it possible that $btn will not equal "Go" AND $status will not be empty? If a condition like that existed, you would run no query at all.One way to test your query statements is to temporarily hard-code a value into the query. I mean, use a known value for odate (one that exists in the table already) and see if the query works.

[Viper114]

This is certainly better. I was thinking of suggesting that you wrap the second query in an if-structure. Is it possible that $btn will not equal "Go" AND $status will not be empty? If a condition like that existed, you would run no query at all.One way to test your query statements is to temporarily hard-code a value into the query. I mean, use a known value for odate (one that exists in the table already) and see if the query works.

So like an If(IfElse) method? I could try that, too, see what happens.

[jeffman]

The normal pattern to make sure there is a default condition looks like this:

if (condition1) {   // do something} elseif (condition2) {   // do something else} else {   // do the default action}

You can have more than one elseif structure in the middle part, too.

[Viper114]

Seems like the one I mentioned seem to do the trick! The tool now works properly and helps the people who use immensely. Thanks for the help!