Page 1 of 2

Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 4:15 am
by simonmlewis

Code: Select all

$sqlconn=@mysql_connect("localhost","user","pass");
$rs=@mysql_select_db("dbname",$sqlconn);
mysql_query("SELECT * FROM matternudge WHERE matterno = $matterno");
if (mysql_num_rows($result)==0) 
  { 
  $sqlconn2=@mysql_connect("localhost","user","pass");
  $rs2=@mysql_select_db("dbname",$sqlconn2);
  mysql_query("INSERT * INTO matternudge(matterno, nudgedate) VALUES ('$matterno', '$nudgedate')");
  mysql_close($sqlconn2); 
  }
else
  {
  while ($row = mysql_fetch_object($result))
      {
      $sqlconn3=@mysql_connect("localhost","user","pass");
      $rs3=@mysql_select_db("dbname",$sqlconn3);
      mysql_query ("UPDATE matternudge SET nudgedate = '$nudgedate' WHERE matterno = '$matterno'");
      mysql_close($sqlconn3); 
      }
    }
 
    mysql_free_result($result);
    mysql_close($sqlconn);
The purpose of this is to see if there is a date in a field. If there is a date, then update it with a date fed from the previous page. If there isn't a date, then insert the date from the previous page.

I don't know if I am making this too complicated in my code.

The 'previous page' has a button on it. It can only be used once in a day. When it's pushed, it fires an email, but records the date it was pushed. So if the date is equal to today, it becomes disabled - hence why I need to see what date is on the system.

The error I get is on

Code: Select all

if (mysql_num_rows($result)==0)
and

Code: Select all

mysql_free_result($result);
Regards
Simon

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 4:32 am
by requinix
It would help if you told us that the error message says something about expecting a resource and getting a boolean...

Code: Select all

mysql_query("SELECT * FROM matternudge WHERE matterno = $matterno");
Print the query before executing it, then look at it and see if what's wrong with it.

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 4:44 am
by simonmlewis
I'm sorry I don't know what you mean.

What have you changed in that code you quoted?

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 4:59 am
by requinix
I didn't change anything, I just posted a part of your code so you knew what I was talking about. Otherwise it wouldn't be so obvious what "the query" to print was.

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 5:02 am
by simonmlewis
Oh - my apologies.

I am just looking online to find how to print a query, as I have never done it.
While I am looking, could you save me the time as I am sure it'll be something like:

echo $my_query or something...

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 5:32 am
by requinix
Sure. You're close but I'm not sure you have the full picture.

Code: Select all

mysql_query("SELECT * FROM matternudge WHERE matterno = $matterno");
The query is right there in the mysql_query. It's kinda hard to do anything with it right now so stick it into a variable and deal with it from there.

Code: Select all

$query = "SELECT * FROM matternudge WHERE matterno = $matterno";
mysql_query($query);
Now, printing the query is just a matter of using echo or print. There's no real difference between the two so you can pick either one.

Code: Select all

$query = "SELECT * FROM matternudge WHERE matterno = $matterno";
echo $query;
mysql_query($query);
Run that code and the query should appear on the page. Then look at the query, try running it in phpMyAdmin or something like that, and try to see where the problem is.

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 6:15 am
by simonmlewis
SELECT * FROM matternudge WHERE matterno = 0103
This is all that echoing produces. 0103 is the correct number.

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 6:55 am
by requinix
If the query looks fine try using mysql_error to find out what the error was.

Here's a trick you can use:

Code: Select all

mysql_query(...) or die(mysql_error())
If there's a problem then mysql_query will return false. That's why you get an error whenever the code tries to use $result - it's false when it should be a resource.
Thing is, when mysql_query returns false the "or die" kicks in and PHP will quit while printing whatever mysql_error returned.

Put that into your code and you'll see an error message.

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 8:02 am
by simonmlewis
Well..... it didn't produce an error - nothing different that the one before based on the lines I explained.

I was wondering if I was allowed to put nested SQL in like this, but I'm assuming I can.

It's most peculiar.

Code: Select all

 
$query ="SELECT * FROM matternudge WHERE matterno = $matterno" or die(mysql_error());
echo $query;
mysql_query($query);

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 8:15 am
by VladSun
Hello, again :twisted:

Why do you need to open 3 connections to the same server and database? It's not needed.

Also, your numeric format - "0103" looks like it is char type field so put it in single quotes in all queries.

And finally, in your while loop you don't use $row values anywhere - so, why do you do this?

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 8:41 am
by simonmlewis
Why do you need to open 3 connections to the same server and database? It's not needed.
I know of no other way to do this.
Also, your numeric format - "0103" looks like it is char type field so put it in single quotes in all queries.
They are... aren't they? It is numeric only.
And finally, in your while loop you don't use $row values anywhere - so, why do you do this?
No need, as I am not rendering anything, just checking if there is a value. If there isn't, I insert a value or update it. I'm not placing anything on screen.
Essentially, this page won't even be seen!

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 8:55 am
by requinix

Code: Select all

$query ="SELECT * FROM matternudge WHERE matterno = $matterno" or die(mysql_error());
echo $query;
mysql_query($query);
Almost. The "or die" was paired with the mysql_query, like this:

Code: Select all

$query ="SELECT * FROM matternudge WHERE matterno = $matterno";
echo $query;
mysql_query($query) or die(mysql_error());

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 9:16 am
by VladSun
simonmlewis wrote:
Why do you need to open 3 connections to the same server and database? It's not needed.
I know of no other way to do this.
Simply do not open/select_db more than once in your script.
simonmlewis wrote:
Also, your numeric format - "0103" looks like it is char type field so put it in single quotes in all queries.
They are... aren't they? It is numeric only.
This leading zero suggests that it is char typed field. Check it ...
simonmlewis wrote:
And finally, in your while loop you don't use $row values anywhere - so, why do you do this?
No need, as I am not rendering anything, just checking if there is a value. If there isn't, I insert a value or update it.
But still you do not use $row values for any "checking if there is a value", so you don't need this loop.

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 9:33 am
by simonmlewis
SELECT * FROM matternudge WHERE matterno = 0103
This is the result I am getting with the 'or die' suggestion, and by removing the other connections from the last suggestion.

Code here:

Code: Select all

$sqlconn=@mysql_connect("localhost","u","p");
$rs=@mysql_select_db("db",$sqlconn);
$query ="SELECT * FROM matternudge WHERE matterno = $matterno";
echo $query;
mysql_query($query) or die(mysql_error());
if (mysql_num_rows($result)==0) 
  { 
    mysql_query("INSERT INTO matternudge (matterno, nudgedate) VALUES ('$matterno', '$nudgedate')");
    }
else
  {
  while ($row = mysql_fetch_object($result))
      {
        mysql_query ("UPDATE matternudge SET nudgedate = '$nudgedate' WHERE matterno = '$matterno'");
        }
  }
 
    mysql_free_result($result);
    mysql_close($sqlconn);
It hasn't produced a particular error. Only...
Warning: mysql_num_rows(): 3 is not a valid MySQL result resource in /
.... file string and line number corresponding to:

Code: Select all

if (mysql_num_rows($result)==0)
and
Warning: mysql_free_result(): 3 is not a valid MySQL result resource in
in same file string corresponding to:

Code: Select all

mysql_free_result($result);

Re: Very complicated SQL Query with 2 nested - not working

Posted: Wed Jan 21, 2009 9:57 am
by VladSun
You don't assign value to $result anywhere in your code ...
http://php.net/mysql_query