Page 1 of 2
[SOLVED] Query - Can't get desired results
Posted: Mon Oct 04, 2004 1:01 pm
by cdickson
I am bug-eyed

from trying to figure out what I'm doing wrong here.
I cannot get results from this query to save my life. I even tried copying and pasting code from a query that IS working, then changing the details, but to no avail.
This query produces only the
$orderdate result - nothing else:
Code: Select all
//Define query
$query = "SELECT * FROM orders, Login_users WHERE orders.date = '$selectdate' ORDER BY orders.order_no ASC";
$r = mysql_query ($query);
$row = mysql_fetch_assoc($r);
//Convert order date format
$date = $row['date'];
$date_arr = explode('-', trim($date));
$order_date = ($date_arr[1].'-'.$date_arr[2].'-'.$date_arr[0]);
echo "<b>Orders Placed on: $orderdate</b><br /><br />";
// Run query
if ($r = mysql_query ($query)) {
// Retrieve and print every record
while ($row = mysql_fetch_array ($r)) {
echo "{$row['lender_name']}<br />
Order No. {$row['order_no']}<br /><br />
{$row['collateral_owner']}<br />
{$row['collateral_address']}<br />
{$row['collateral_city']}, {$row['collateral_state']} {$row['collateral_zip']}<br />
Loan No.: {$row['loan_no']}<br />
<hr noshade size="1" />";
}
} else { // Query didn't run.
die ('<p>Could not retrieve the data because: <b>' . mysql_error() . "</b>. The query was $query.
Please report this error to Company Name.</p>");
} // End of query IF.
mysql_close(); // Close the database connection.
... but if I change Line 5 to:
Code: Select all
$query = "SELECT * FROM orders, Login_users WHERE Login_users.lender_name = '$selectLenders' AND orders.userid = Login_users.userid ORDER BY orders.order_no DESC";
...I get all of the results I am looking for.
Can anyone see what small thing am I missing?
Posted: Mon Oct 04, 2004 1:07 pm
by feyd
your first query doesn't actually join the tables together in any fashion.
Posted: Mon Oct 04, 2004 1:10 pm
by AGISB
Code: Select all
AND orders.userid = Login_users.userid
is the important part. Without this you cannot get the matching result of both tables
Posted: Mon Oct 04, 2004 2:43 pm
by cdickson
Sorry. Neglected to report that I have tried that query as well. I still can't get any results.
Posted: Mon Oct 04, 2004 3:01 pm
by feyd
post the table structures, and how they are supposed to link up. (what's the same between them)
Posted: Mon Oct 04, 2004 3:44 pm
by cdickson
Sorry I didn't include that - see below.
The
userid field is the common field between the two tables.
I have been able to generate
numerous reports by using the basic query format that I first posted - it's just this one "results by date" query that has me stumped.
Login_users table
Code: Select all
Field Type Null Key Default Extra
userid int(35) PRI NULL auto_increment
username varchar(40)
password varchar(255)
email varchar(255)
f_name varchar(100)
l_name varchar(100)
activated enum('0','1') 0
user_level enum('0','1','2','3') 0
last_login datetime 0000-00-00 00:00:00
decrypt_pass varchar(12)
lender_name varchar(60) MUL
lender_address varchar(30)
lender_city varchar(30)
lender_state varchar(10)
lender_zip varchar(10) 0
lender_phone varchar(12) 0
lender_fax varchar(12)
lender_id int(10) 0
orders table
Code: Select all
Field Type Null Key Default Extra
order_no int(11) PRI NULL auto_increment
userid int(35) 0 <=FK - common field
date date 0000-00-00
collateral_owner varchar(60)
collateral_address varchar(60)
collateral_city varchar(30)
collateral_state varchar(20)
collateral_zip int(10) 0
collateral_county varchar(30)
collateral_twp varchar(30) YES NULL
legal_descr longtext YES NULL
loan_no int(10) 0
ins_amount int(10) YES NULL
community_name varchar(60)
community_no int(10) 0
map_no int(10) YES NULL
map_eff_date date YES 0000-00-00
loma_lomr varchar(10) YES NULL
loma_lomr_date date YES NULL
flood_zone varchar(20) YES NULL
nfip_map varchar(10) YES NULL
ins_avail char(3)
reg_program varchar(10)
emergency varchar(10)
ins_notavail varchar(10)
bldg_CBRA char(3)
CBRA_date date YES 0000-00-00
flood_hazard char(3) YES NULL
comments longtext YES NULL
census_tract int(10) YES NULL
msa_smsa int(6) YES NULL
state_code int(3) YES NULL
county_code int(4) YES NULL
Posted: Mon Oct 04, 2004 3:50 pm
by feyd
Code: Select all
SELECT * FROM Login_users a INNER JOIN orders b ON b.userid = a.userid WHERE b.date = '$selectdate' ORDER BY b.order_no
Posted: Tue Oct 05, 2004 10:00 am
by cdickson
Thanks feyd, but this isn't working either.
I figure you have pointed me in the right direction, so I am still working on it.

Posted: Tue Oct 05, 2004 10:34 am
by feyd
check to make sure you are sending the right $selectdate
Posted: Tue Oct 05, 2004 10:50 am
by cdickson
Thanks - checked that first - see Line 15.
Code of form that is submitted to get desired results:
Code: Select all
<?php
//View orders by order date
$query3 = "SELECT DISTINCT(date) FROM orders ORDER BY date DESC";
$result3 = mysql_query($query3);
$row3 = mysql_fetch_assoc($result3);
//
<form name="selectdate" method="post" action="results-date.php">
<table width="420" border="0" align="center" cellpadding="5" cellspacing="2">
<tr class="navtext">
<td colspan="2"> <b>View List of Orders - Select
by Order Date </b><br />
Shows Lender & Property Info</td>
</tr>
<tr class="navtext">
<td><select name="selectdate">
<?php
do {
?>
<?php
//Convert order date format
$date = $row3['date'];
$date_arr = explode('-', trim($date));
$order_date = ($date_arr[1].'-'.$date_arr[2].'-'.$date_arr[0]);
?>
<option value="<?php echo $order_date; ?>"><?php echo $order_date; ?></option>
<?php
} while ($row3 = mysql_fetch_assoc($result3));
$rows = mysql_num_rows($result3);
if($rows > 0) {
mysql_data_seek($result3, 0);
$row3 = mysql_fetch_assoc($result3);
}
?>
</select></td>
<td><div align="right">
<input name="Submit3" type="submit" class="navtext" id="Submit3" value=">> Go" />
</div></td>
</tr>
</table>
</form>
Posted: Tue Oct 05, 2004 11:01 am
by feyd
and you have register globals on?
Posted: Tue Oct 05, 2004 11:14 am
by cdickson
Yes. But in the event that they aren't, I have a file included at the top of the page that addresses the globals issue.
Posted: Tue Oct 05, 2004 11:16 am
by feyd
any you have echo'd your query in the processing page to see that it is indeed correct?
Posted: Tue Oct 05, 2004 11:36 am
by cdickson
Now I have to admit to you how noob I still am.
I haven't done this, because I'm not sure how. So on the form that is being submitted I tried this after the query:
Code: Select all
echo $row3;
echo $result3;
echo $rows;
Results produced are:
echo $row3; =
Array
echo $result3; =
Resource #9
echo $rows; =
22
From this I gather that something in the query is totally wrong, because 22 records should be produced. Is this right, or do I get demoted to noob again?
Posted: Tue Oct 05, 2004 12:38 pm
by feyd
Well.. echoing the result from mysql_query, if executed correctly, should print "Resource #" followed by a number, so it looks like it's performing correctly. I would assume $rows is the result of a mysql_num_rows call, so it looks right..