Very complicated SQL Query with 2 nested - not working

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Very complicated SQL Query with 2 nested - not working

Post 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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

I'm sorry I don't know what you mean.

What have you changed in that code you quoted?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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...
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

SELECT * FROM matternudge WHERE matterno = 0103
This is all that echoing produces. 0103 is the correct number.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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);
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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());
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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);
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

You don't assign value to $result anywhere in your code ...
http://php.net/mysql_query
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply