Need Help defining Query

[n1concepts]

I need to define a query in a PHP script that will check a MySQL db - based on six variables (the six variables are statically defined

within the script based on another snippet of code reading the new file that's being prepared for insertion into the db.)

 

Objective: if the six variables, from the new records, does not produce an exact match already within the db based on ($rcheck)

then insert the new record into the db. Note: I have the INSERT statement working correctly but having issues with the query ($rcheck).

 

As for the records being checked by ($rcheck) in the MySQL db: they are all UNIQUE records based on the six fields

I'm trying to build the query around to ensure no DUPLICATE entries are made with new insertions.

-------------------

 

Issue: I can't get the query ($rcheck) to fire correctly to check the queued (new records' data) against the db - based on six fields of data.

Error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

 

FYI: if I just do a standard "SELECT * FROM information"; the quey works as it should but I need to check the db based on the six fields as shown in below code:

 

$rcheck = "SELECT docno,fdate,ftime,vegregno,direction,grossmass FROM information where docno = $docnoNew 
AND fdate = $fdateNew AND ftime = $ftimeNew AND vegregno = $vegregnoNew AND direction = $directionNew 
AND grossmass = $grossmassNew;";

$duplicate = mysql_query(rcheck);    // Execute query to check for duplicate transaction in table

        while ($row = mysql_fetch_array($duplicate)) {

            echo $row['docno']."<br/>";
            echo $row['fdate']."<br/>";
            echo $row['ftime']."<br/>";
            echo $row['vegregno']."<br/>";
            echo $row['direction']."<br/>";
            echo $row['grossmass']."<br/>";
}

 

I know the problem rests with trying to use multiple "AND" clauses but I'm not sure how to get around this issue.

Any advice on how to define this query to check based on all six fields surely appreciated to get ($rcheck) working!

 

Thx

 

[Pikachu2000]

Don't you have another thread open on this topic, in which I already pointed out that rcheck is not the same as $rcheck in this:

$duplicate = mysql_query(rcheck); ?

[n1concepts]

Yes and I did update that syntax issue to the following: $duplicate = mysql_query($rcheck);

 

However, that's not the cause of the actual query not working - which I opened this new thread to explain.

The problem is with this line of code:

 

$rcheck = "SELECT docno,fdate,ftime,vegregno,direction,grossmass FROM information where docno = $docnoNew

AND fdate = $fdateNew AND ftime = $ftimeNew AND vegregno = $vegregnoNew AND direction = $directionNew

AND grossmass = $grossmassNew;";

-----

 

Do you mind commenting (hopefully with a solution) to the above line which is where I'm having the problem?

Again, I appreciate your input and acknowledge this is the same problem but last thread was closed so there wouldn't be confusion.

 

I need help with the posted line of code in this comment please (thx!)

[Pikachu2000]

Well, you posted old code with the same error, so what am I supposed to think?

 

Change that line to read

$duplicate = mysql_query($rcheck) or die("<br>Query string: $rcheck<br>Returned error: " . mysql_error() );

[n1concepts]

True - I apologize for that (I copied the code from last thread but failed to update that issue; thank you).

 

However, I don't think you're grasping the question I'm presenting:

 

SELECT docno,fdate,ftime,vegregno,direction,grossmass FROM information where docno = $docnoNew

AND fdate = $fdateNew AND ftime = $ftimeNew AND vegregno = $vegregnoNew AND direction = $directionNew

AND grossmass = $grossmassNew

------

 

I need the correct method of executing the above line of code (which is the $rcheck variable).

Note: I understand how to throw / produce an error if $rcheck fails (not what I'm asking for help on....)

 

I need a valid "SELECT" statement which queries the MySQL db based on the six "AND" clauses defined in the above statement.

Note: I've deduced the problem down to this one line of code - if I just perform a standard query with one or two AND clauses, it works just fine. However, I need all six defined to match on each of those fields.

 

That's the issue - I need someone to provide a valid "SELECT" statement based on the six AND clauses which (for me) is failing to work properly.

 

Thanks and appreciate your response but not what I was looking for regarding this matter.

[Pikachu2000]

I do understand, and returning the error, along with the interpolated query string so you can see exactly what is being fed to MySQL will help you figure out exactly what is wrong with the query string.

[PFMaBiSmAd]

There's nothing technically wrong with the query, provided that all the data values you put into it are numbers and not strings and all the columns exist...

 

The code Pikachu2000 has posted here and probably in the other thread as well contains some debugging logic (the or die() statement) that would help you if you were actually reading what he has been posting.

[n1concepts]

Yes, I did and understand what I was provided did show me the error - really appreciate!!!!! 

 

Thanks to Pikachu2000 guidance, I now see the problem is with the $ftime variable which is the "time" in the format of hh:mm:ss.

Issue: the colon is causing a MySQL syntax issue within the SELECT statement.

 

Attempted solutions:

1. I edited the $ftime variable (encapped $ftime with ADDSLASHES but no success)

2. I edited the actual satement in the "SELECT" query with a static date - to test; escaping the colons in ftime with preceeding backslash but no success.

 

Note: I tried all I know before asking on the forum but need guidance as all my searches on the Net not giving me answers - that I understand.

 

See the error below based on last script execution:

 

Query string: SELECT docno,fdate,ftime,vegregno,direction,grossmass FROM capstone_transact.transactions where docno = 104980 AND fdate = 05/14/2010 AND ftime = 23:55:11 AND vegregno = XLX885GP AND direction = OUT AND grossmass = 20640;

 

Returned error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':55::11 AND vegregno = XLX885GP AND direction = OUT AND grossmass = 20640' at line 2

-----

 

Here's the code that supports the $ftime variable used in the SELECT statement:

 

$ftimeT = $eof->Time1;                                   // Time1 is stripped and reconstructed for display hh:mm:ss
$rawTime1 = addslashes($ftimeT);                // I know the extra ADDSLASHES may be overkill but wanted to make sure
$ftimeNew = addslashes(substr($rawTime1,11,);

 

Results: will show time as 23:11:03 (example but all data in this format).

 

Q: how do I escape the colon to fix this issue in the SELECT statement above?

[Pikachu2000]

You'll need to enclose it in single quotes in the query string.

. . . AND ftime = '$ftimeNew' AND . . .

 

Strings get quoted in a query string, whereas numeric values shouldn't be.