MySQL Query Processing order [RESOLVED]

[ALundrigan]

Is it just me, or does MySQL seem to process queries in reverse order sometimes?

I have this script which i'm working on. What it does is check a database table for a specific username, and if the username doesn't exist in the database then it inserts that username as a new row. ie:

...

if(strlen($_POST['username']) == 0)
    $error[] = getString('profile_error_quote_blank');
else
{
    $res = $db->sql_query("SELECT * FROM stocks WHERE quote LIKE \"$_POST[username]\" LIMIT 1");
    if($db->sql_numrows($res) > 0)
        $error[] = getString('quote_taken');

}

...

if(isset($error) && count($error) > 0)
{

    require_once("stock_newform.inc.php");

}
else
{

    if(getConfig('use_stock_verification') == 'N')
    {
        $sql = "INSERT INTO stocks (rootid,quote,name,email,url,joindate,region,country,type,description,keywords,pending) VALUES ('$mi[id]', '$username', '$name', '$email', '$website', '$jd', '$region', '$country', '$sector','$description','$keywords','N')";
        $res = $db->sql_query($sql);


... 

When I execute the script with what I know is a username that doesn't exist in the database, it displays the error '$error[] = getString('quote_taken');'. It seems to be that the 'INSERT INTO' query gets executed before the 'SELECT * FROM' query, and thus the new username is added to the table before the check is done to see if it exists. Is this normal?

I've never had this problem before, with other scripts that do this same type of error checking.


Windows NT 5.1 (HOME)
Apache Web Server 2.0.48 (Win32)
PHP 4.3.4
MySQL 4.1.0-alpha

[The Hobo]

I don't think that's possible. Looking at your code, I don't see how the second SQL could be called first.

It's possible that the first one isn't getting called at all, or that something else is fudged in your code.

I'd add a bunch of echo statements in certain spots to test the output and see which code gets executed.

Like within the ifs, put stuff like:

echo 'spot #1<br />';

And when you run it, compare the output to what you know should be executed...and you should be able to see where it's going wrong.

[kows]

I don't think it's executing the second query first.. but, well, maybe it is. When PHP is parsing the file it reads it line by line an doesn't skip around anything unless it's something like a Do loop that has it's conditions already met or an if statement where the conditions don't mix. I don't think it's even possible for your second query to fire first, not unless that code is in your file before the first query.

Try putting some breakpoint echo's in the code around the first query and the same around the second query and see what happens when it's executing..

[ALundrigan]

... 

if(strlen($_POST['username']) == 0) 
    $error[] = getString('profile_error_quote_blank'); 
else 
{ 
    $res = $db->sql_query("SELECT * FROM stocks WHERE quote LIKE \"$_POST[username]\" LIMIT 1"); 
    if($db->sql_numrows($res) > 0) 
        $error[] = getString('quote_taken'); 

} 

... 

if(isset($error) && count($error) > 0) 
{ 

    echo "HELLO WORLD 1";

    require_once("stock_newform.inc.php"); 

} 
else 
{ 

    echo "HELLO WORLD 2";

    $sql = "INSERT INTO stocks (rootid,quote,name,email,url,joindate,region,country,type,description,keywords,pending) VALUES ('$mi[id]', '$username', '$name', '$email', '$website', '$jd', '$region', '$country', '$sector','$description','$keywords','N')"; 
        $res = $db->sql_query($sql); 

        ...

        $emailtemp = LoadEmail('new_stock_activation');
        DispatchEmail($email,$emailtemp['title'],ParseEmail($emailtemp['message'],$parsedetails));

        CreatePage(str_replace("\$email",$email,getString('signup_must_activate_account')),400);


... 

I have no idea what i've done with this script....its a freak of nature :P

When I put in the echo statements, only 'HELLO WORLD 1' gets executed but the 2nd query ('INSERT INTO ...') still gets executed and the emails still get sent ( LoadEmail(...) / DispatchEmail(...) ). This is technically impossible, since they are in mutually exclusive sections of an if block

If I put the CreatePage(...) function call inside a die() then everything works fine, which leads me to believe there is a misplaced call somewhere that is causing something like this:

1) 1st query is called, returns no error
2) Because 1st query passed, 2nd query gets called
3) Page gets refreshed or reloaded automatically
4) 1st query is called again, returns an error since 2nd query already inserted the data that 1st query is looking for.

Why 3 happens is beyond me :| Perplexing, indeed



Anyway, thanks guys for your help. I'll just put in a die() around CreatePage(...) for now and come back some other time to reprogram the whole thing.