-1

Running a program which includes two queries. When I run either query alone it works but when both queries exist within the code it breaks. 

$qry = "SELECT * FROM temp_user WHERE email='$email' AND pin='$pin'";
$result = mysql_query($qry);
$num_rows = mysql_num_rows($result);

$qry2 = "SELECT * FROM email WHERE email='$email'";
$result2 = mysql_query($qry2);
$num_rows2 = mysql_num_rows($result2);

How can I fix this?

EDIT:

For those asking what do I mean by it breaks, here is an image.

enter image description here

Error from log.

PHP Parse error: syntax error, unexpected '}' in /Users/philipkirkbride/Documents/apps/Today_test/confirm.php on line 30

Full code of page is

<?php
include 'connect.php';

$pin = $_GET['pin'];
$email = $_GET['email'];

$qry = "SELECT * FROM temp_user WHERE email='$email' AND pin='$pin'";
$result = mysql_query($qry);
$num_rows = mysql_num_rows($result);
if ($num_rows!=0){
    print "create user and delete temp";
    $sql = "INSERT INTO pin VALUES (DEFAULT, '$pin', '$email')";
    $result = mysqli_query($con,$sql);
    if ( false===$result ) {
      printf("error: %s\n", mysqli_error($con));
    } else {
        // Delete user from temp table
        $sql2 = "DELETE FROM temp_user WHERE email='$email' AND pin='$pin'";
        $delete = mysqli_query($con,$sql2);
        // Make query to see if user is new or existing
        $qry2 = "SELECT * FROM email WHERE email='$email'";
        $result2 = mysql_query($qry2);
        $num_rows2 = mysql_num_rows($result2);
        // Need to add a snippet to add a row to the email table, make sure to check user doesn't have an email already in table
        if($num_rows2==0){
            print "email doesn't exist, create new user."
            // $date = new DateTime;
            // $sql = "INSERT INTO email VALUES ('$email', '$date')";
            // $result = mysqli_query($con,$sql);
        }else{
            print "email exists already";
        }
    }
}else{
    print "Account request not found";
}
// End connection
mysqli_close($con);
?>
Improve this question
14
  • 9
    Can you give us more than "it breaks"? Commented Apr 17, 2013 at 22:08
  • 2
    What do you expect to happen? What really happens? What have you tried to fix it and where did that attempt fail? Commented Apr 17, 2013 at 22:09
  • 1
    not related to answer, just to propogate good behavior ... mysql is deprecated. please use mysqli. Commented Apr 17, 2013 at 22:12
  • 2
    if you're writing new code, it's worth telling you that PHP has deprecated the mysql_xxx() functions. For new code you should use the PDO library instead. (if it's existing code that already uses mysql_xx() then carry on, but you need to make plans to convert it, as future versions of PHP will drop the old mysql extension. Commented Apr 17, 2013 at 22:13
  • 2
    ...you mean aside from the fact that it isn't working, right? Commented Apr 17, 2013 at 22:21

2 Answers 2

Reset to default
4

Try calling $result->close() before you call the second query, in order to release the connection. Your PHP script hasn't finished executing yet, and your previous result is holding that connection open. In general, it is good practice to explicitly clean up these kinds of resources rather than leaving it to the runtime.

EDIT: I believe @eis is correct that since this is the older (and deprecated, incidentally) mysql_ api, that the proper call would actually be mysql_free_result($result).

ANOTHER EDIT: I put this comment under @eis's answer, but I figure it bears bolder type, because @eis's answer was absolutely right and should not have been down-voted:

Just to elaborate redundantly on the whole buffered vs. unbuffered thing; the concept is that a buffered query will read all of the results back from the database into a buffer, then release the connection automatically (this will happen synchronously, so that the connection is free for another call immediately after the buffered call returns). An unbuffered query will essentially open the connection and hand back a reference to a database server cursor, from which you will then need to fetch records. As long as there is something left to fetch, the cursor is still open, and you won't be using the underlying connection for anything else. Or you can call mysql_free_result() and free the connection "early" (without reading all of the rows from the cursor first).

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

as this is legacy mysql_ api, I do think you need to use mysql_free_result($result) instead of $result->close(), like I proposed (and got downvoted for :p)
I was surprised you got downvoted. I might add that I have read elsewhere that PDO isn't really used by folks inside Oracle and they aren't all great-guns about maintaining it, so +1 for recommending mysqli.
Did php.net really just go down? I guess that earlier "mysql_query() returns an object...really?" comment disappeared. I'm not sure what one would expect it to return; a poodle? According to the docs, it returns a resource, or false. A "resource" (ahem) would be one of them there object thingeys, seeing as how a resource is defined as a reference to external stuff, "external stuff" being specifically defined only in the context of the specific resource type.
You're probably right about needing to call mysql_free_result($result) instead of ->close().
objects weren't around in PHP at the time mysql_ was there. It came as part of mysqli_. For mysqli_ results, you can call ->close().
|
3

you should actually either 1) read the result that you retrieved or 2) free it (mysql_free_result)...

You have two distinct queries. If you don't read the result or close it in between, it might not let you run a new one.

(I don't see the point doing the queries if you don't actually need the actual items. Use SELECT COUNT(*) if you are not intrested in the actual items, and just about the count.)

Improve this answer

12 Comments

would be interested to get feedback about the -1
It wasn't my vote, but you're not actually answering the question.
I am. The question is "how should I fix this". It will be fixed by reading the result in between the queries.
mysql_ API won't allow you to run two distinct queries without reading the result in between...
but ok, if these would be unbuffered, mysql_num_rows wouldn't work either... so I guess I'm wrong then.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.