I have a simple table that I need to compare the TL fileds and produce a report only if the TL fields matches and the Status feild equals Buy and Sell.
I want to match any inventory that is the same TL and I am buying, selling, and report that from my database. I am copying a simplified version of the table as an example.
I am using PHP and MySQL as tools and cannot figure out how to comapire all the fields, as I am a rookie at best.
ID RefNo TL Status Status_Date
56 25 DryVan Sell 2006-06-21
57 25 DryVan Buy 2006-06-30
I think I am following you but getting an erroe when trying.
(Parse error: parse error, unexpected T_STRING in /home/trailerr/public_html/AS.php on line 52)
Line 52 is and status in ('Buy', 'Sell')";
Here is what I did from your example;
PHP Code:
mysql_select_db($dbname);
$result = mysql_query ('" SELECT tractortype.TractorMake FROM tractortype as a,tractortype as b WHERE tractortype.TractorMake=tractortype.TracortMake and status in ('Buy', 'Sell)"); or die("Query failed : " . mysql_error());
while ($row = mysql_fetch_assoc($result))
echo "<a class=titler>Tractors Matched</a> as of ".date("F d, Y");
That worked but not producing any information for the table. The table over 100 entires and shuld match 5 times as the TT field matches Sell with the TTfield macthes Buy.
Does the "FROM tractortype as a,tractortype as b" statment need to be modified?
PHP Code:
<?php
// section for tractors require_once('settings.inc');
mysql_select_db($dbname);
$result = mysql_query (" SELECT tractortype.TT FROM tractortype as a,tractortype as b WHERE tractortype.TT=tractortype.TT and status in ('Buy', 'Sell')") or die("Query failed : " . mysql_error());
while ($row = mysql_fetch_assoc($result))
echo "<a class=titler>Tractors Matched</a> as of ".date("F d, Y");
smarlar, welcome to Coding Forums. Please use [ php] and [/php] tags around PHP and use [ code] and [/code] around regular mysql code. It will help your code display much nicer and easier to read. Remove the space of course.
I have added the tags in both your posts above. If you look at them you will see that even the code displays differently.
May I also offer a piece of advice. When getting help with a query always test the query directly in mysql to see if the query is working correctly. Once you know it is working correctly, then any error after that must be as a result of PHP or ColdFusion or whatever other application you run your mysql query in.
Last edited by guelphdad; 07-21-2006 at 04:03 PM..
I think the problem is that you don't understand what the aliases are doing and how to use them.
When you use an alias to refer to a table then you must refer to the table with that alias afterwards.
If TRACTORTYPE is the name of your table and you alias it as "a" and "b" then you no longer refer to the table as TRACTORTYPE but as "a" or "b".
Try your query like this:
PHP Code:
$result = mysql_query (" SELECT tractortype.TT FROM tractortype as a,tractortype as b WHERE a.TT=b.TT and status in ('Buy', 'Sell')") or die("Query failed : " . mysql_error());
Thanks for the input as I am getting closer after testing the SQL over and over this what I am coming up with wich returns all the records, but when I use the A.TL !=b.TL I get no results. I should have one match after looking at the database.
I am using the SELECT * because I am reporting on 6 fields not just one.
PHP Code:
mysql_select_db($dbname);
$result = mysql_query
("SELECT *
FROM trailertype as a, trailertype as b
WHERE a.TL !=b.TL
AND Status in ('Buy', 'Sell')")
or die("Query failed : " . mysql_error());
You want to be using = there, not !=.
What fumigator meant was in addition, not instead of, and should probably be < (or >) rather than !=, otherwise you'll end up with twice as many results, having both permutations (combinations? maths was so long ago).
Code:
SELECT * FROM trailertype a, trailertype b WHERE a.TL=b.TL AND status IN ('Buy','Sell') AND a.id < b.id;
I need to return the results "Dump Bed" as they are the only two that match both TL's and the Status is Sell and Buy. I am using MySQl version 4.0.16 if that makes a difference. Here are the results thus far.
PHP Code:
("SELECT *
FROM trailertype
WHERE trailertype.TL=trailertype.TL
AND Status='Buy'")
Returns all records with the Status types of Buy
PHP Code:
("SELECT *
FROM trailertype
WHERE trailertype.Status<>trailertype.Status
AND TL=TL")
Returns all records that TL that equals TL
PHP Code:
("SELECT *
FROM trailertype a, trailertype b
WHERE a.TL =b.TL
AND Status='Buy'")
Returns nothing
PHP Code:
("SELECT *
FROM trailertype as a, trailertype as b
WHERE a.TL !=b.TL
AND Status in ('Buy', 'Sell')")
Returns nothing
I have tired all possible combinations in My Sql admin panel and the same results are the same. Confused and dazed at this point, or do I need to copy all records to two separate databases to achieve the desired results, or do I need another variable to make this happen?
Before you post, read our: 
