[SOLVED] Query - Can't get desired results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

[SOLVED] Query - Can't get desired results

Post by cdickson »

I am bug-eyed 8O 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your first query doesn't actually join the tables together in any fashion.
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Sorry. Neglected to report that I have tried that query as well. I still can't get any results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

post the table structures, and how they are supposed to link up. (what's the same between them)
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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. :?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

check to make sure you are sending the right $selectdate
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

and you have register globals on?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

any you have echo'd your query in the processing page to see that it is indeed correct?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Now I have to admit to you how noob I still am. :oops:
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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
Post Reply