Count(*) + 1 = Infinity

[rubing]

I am trying to count the number of results and then add 1 to it as follows:

 

$resnum=mysql_query("SELECT COUNT(*) FROM MusicEvents WHERE Date = '$today' AND Venue < '$message_final'");
$num_rows=mysql_result($resnum,0,"COUNT(*)");
$num_rowsplus=$num_rows + 1;

 

$num_rows works fine and returns the number of results.  However, it doesn't work if I try to add 1 to it ($num_rowsplus).  What gives!!???

 

THanks!

[cooldude832]

<?php
$q = ""SELECT COUNT(*) as e_count FROM MusicEvents WHERE Date = '".$today."' AND Venue < '".$message_final."'";
$r = mysql_query($q) or die(mysql_error()."<br /><Br />".$q);
list($num_rows) = mysql_fetch_assoc($r);
$num_rowsplus = $num_rows+1;
echo "Rows: ".$num_rows."<br />";
echo "Rows Plus 1: ".$num_rowsplus."<br />";
?>

[Barand]

Unfortunately, cooldude, that won't work.

 

Apart from the obvious parse errors, you can't use list() with an associative array. You need fetch_row

[rubing]

Yes.  I just tried it and it returned null.

 

Also this is not an embedded html script

[Barand]

rubing,

 

I just tried your code with one of my tables and when I echo $num_rowsplus I get he expected result. ???

[rubing]

Really!?? I think it may have to do with the fact that I am running this as an executable autoresponder email script.  Let me try everything again to confirm.

[rubing]

I get the value of $num_rows fine, but $num_rowsplus i can't retrieve. 

 

I think it has something to do with $num_rows being the result of a SQL query.  Is there a way to convert $num_rows to an integer first??

[Barand]

Ir should already be an integer. Try

 

var_dump($num_rows);

[rubing]

Actually it comes up as NULL, but is working  ???  This whole situation is very strange.  Let me start from the start:

 

This is an autoresponder script that I am writing, when somebody sends an email with the statement 'show somevenue' to [email protected] it gets forwarded to this executable script, which responds back with who's playing at the chosen venue.  In order to debug I send it emails and it bounces with an error message.  For some reason I can only execute 1 mysql query per script invocation and also am unable to use mysqli objects, but this is not my main concern.  Let me explain:

 

My script (processtonight.php) begins with a require statement: 

 

#!/usr/bin/php -q
<?phprequire_once "numrows.inc";
require_once "contact.inc";

 

Inside of numrows.inc I look to see if the user replied from there last email with the words 'show next' (show is a marker so, i can find their input, but i filter it out with a regex), so that I can see the last result viewed and increment it by one (assigned to $numrow)

 

if ($message_final == 'next') {
$result=mysql_query("SELECT * FROM phonelog 
WHERE email = '$to' 
ORDER BY last_access DESC 
LIMIT 1");

while ($row = mysql_fetch_assoc($result)){
$message_final=$row["query_word"];
$numrow=$row["result_viewed"];
$numrow+=1;
}
}

 

If the user did not enter 'next', this must be a new query so I determine which alphabetized result to show him/her by executing a COUNT.  This works just fine now!!!  (It's strange but, I need that last line for it to work right: $num_row .= $num_rows;)

 

else {
$resnum=mysql_query("SELECT COUNT(*) 
FROM MusicEvents WHERE Date = '$today' AND Venue < '$message_final'");
$num_rows=mysql_result($resnum,0,"COUNT(*)");
$num_row .= $num_rows;

 

The error I get is this: 

<b>Warning</b>:  mysql_fetch_array(): supplied argument is not a valid

MySQL result resource in <b>/home/rubing/lib/processtonight.php</b> on

line <b>17</b><br />

 

This error refers to my $numrow variable coming from user inputting next.  If I comment it out all is well.  If I mail $numrow to myself I recieve the correct number.

 

 

//long query for alphabetizing my results and ignoring a, an, & the; the important line is last
$query="SELECT MusicEvents.Band, Venue, Category,HomeTown,myspace_address, 
CASE WHEN SUBSTRING_INDEX(Venue, ' ', 1) IN ('a', 'an', 'the') THEN CONCAT(SUBSTRING(Venue, INSTR(Venue, ' ') + 1),	', ', SUBSTRING_INDEX(Venue, ' ', 1) ) 
ELSE Venue END AS VenueSort 
FROM MusicEvents LEFT JOIN Bands ON MusicEvents.Band=Bands.Band 
WHERE Date = '$today' 
ORDER BY VenueSort ASC, Source ASC, MusicEvents.Band ASC 
LIMIT $num_row , 1";  //If I comment out $numrow here it works fine.  

$result=mysql_query($query);while ($row = mysql_fetch_array($result))
{
...code continues

 

[BlueSkyIS]

change this

 

$result=mysql_query($query);

 

to this

 

$result=mysql_query($query) or die(mysql_error()." in $query");

 

[rubing]

I changed it to that, but now it bounces without any error message

[darkfreaks]

try putting in

 

<?php

ini_set('error_reporting',E_ALL); ?>

[rubing]

ahhh darkfreak i love  you...now i got some error messages!!! 

 

 

here they are:

 

<br />

<b>Notice</b>:  Undefined variable:  num_row in

<b>/home/rubing/lib/contact.inc</b> on line <b>5</b><br />

<br />

<b>Notice</b>:  Undefined variable:  num_row in

<b>/home/rubing/lib/processtonight.php</b> on line <b>15</b><br />

<br />

<b>Warning</b>:  mysql_fetch_array(): supplied argument is not a valid

MySQL result resource in <b>/home/rubing/lib/processtonight.php</b> on

line <b>19</b><br />

<br />

<b>Notice</b>:  Undefined variable:  message in

<b>/home/rubing/lib/processtonight.php</b> on line <b>44</b><br />

[darkfreaks]

can u supply the whole code then comment out those lines ???

 

 

for starters take out the following line:

$num_row .= $num_rows;

 

and in yout sql statement change $num_row to $num_rows

[rubing]

Sure, this is the entire program.

 

processtonight.php:

 

#!/usr/bin/php -q
<?php
ini_set('error_reporting',E_ALL);
require_once "numrows.inc";
require_once "contact.inc";
$pattern='[[:alnum:]._-]+@[[:alnum:]-]+\.([[:alnum:]-]+\.)*[[:alnum:]]+';
if(@ereg($pattern, $sendersaddr, $match))
{
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);
$querystr="SELECT MusicEvents.Band, Venue, Category,HomeTown,myspace_address, CASE WHEN SUBSTRING_INDEX(Venue, ' ', 1) IN ('a', 'an', 'the') THEN CONCAT(SUBSTRING(Venue, INSTR(Venue, ' ') + 1), ', ', SUBSTRING_INDEX(Venue, ' ', 1) ) ELSE Venue END AS VenueSort FROM MusicEvents LEFT JOIN Bands ON MusicEvents.Band=Bands.Band WHERE Date = '$today' ORDER BY VenueSort ASC, Source ASC, MusicEvents.Band ASC LIMIT $num_row , 1";  //LINE 15
// $result=mysql_query($query) or die(mysql_error()." in $query");
$res=mysql_query($querystr);

while ($row = mysql_fetch_array($res))  //LINE 19
{
if(!empty($row['HomeTown']) && !empty($row['myspace_address']))
{
$message .=$row['Band'] . " playing at " . $row['Venue'] . " \n" . $row['myspace_address'] . "\n";
}

elseif(!empty($row['HomeTown']) && empty($row['myspace_address']))
{
$message .=$row['Band'] . " playing at " . $row['Venue'] . " \n";
}

elseif(empty($row['HomeTown']) && !empty($row['myspace_address']))
{
$message .=$row['Band'] . " playing at " . $row['Venue'] . " \n" . $row['myspace_address'] . "\n";
}

elseif(empty($row['HomeTown']) && empty($row['myspace_address']))
{
$message .=$row['Band'] . " playing at " . $row['Venue'] . "\n";
}
}

$subject ='Tonight';
$headers = 'From: [email protected]' . "\r\n" . 'Reply-To: [email protected]' . "\r\n" . 'X-Mailer: PHP/' . phpversion();
mail($to, $subject, $message, $headers);
mysql_close();}
?>

 

numrows.inc:

 

<?php
//read email into stdin
$fd = fopen("php://stdin", "r");
$email = "";
while (!feof($fd)) {
$email .= fread($fd, 1024);
}
fclose($fd);
$today = date("Y-m-d");

// extracts query word
$pat='[sS][Hh][Oo][Ww] [[:alnum:]._\'\"&]{1,150}';
@ereg($pat, $email, $query_word);
$lines = explode("\n", $email);
$sendersaddr=$lines[0];
$message_body =$query_word[0];
$nopat=' [[:alnum:]._\'\"&]{1,150}';
@ereg($nopat, $message_body, $query_final);
$message_final =$query_final[0];
$message_final = trim($message_final);
$message_final=addslashes($message_final);

//if valid email insert session data into db
$pattern='[[:alnum:]._-]+@[[:alnum:]-]+\.([[:alnum:]-]+\.)*[[:alnum:]]+';
if(@ereg($pattern, $sendersaddr, $matches))
{
$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'XXXXX';

$to = $matches[0];
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);
//this value will depend on if $message_final = next (via last result viewed)otherwise defaults to 1 ()switch)
//convert $message_final to lowercase
//if its next or previous then lookup in phonelog last viewed result and add or subtract one

if ($message_final == 'next')
{
$result=mysql_query("SELECT * FROM phonelog WHERE email = '$to' ORDER BY last_access DESC LIMIT 1");
while ($row = mysql_fetch_assoc($result))
{

$message_final=$row["query_word"];
$numrow=$row["result_viewed"];
$numrow+=1;
// $numrow=var_export($numrow,true);
// $numrow =(string)$numrow;
//go lookup the value of $numrows in table and add 1
}
}

elseif ($message_final == 'previous')
{
//lookup $numrows and subtract 1
}

else
{
//gonna try to find how many rows there are before the users query
// global $message_final;
$resnum=mysql_query("SELECT COUNT(*) FROM MusicEvents WHERE Date = '$today' AND Venue < '$message_final'");
$num_rows=mysql_result($resnum,0,"COUNT(*)");
$num_row .= $num_rows;
//so now we have an internal pointer
}
mysql_close();
}
?>

 

And finally contact.inc:

 

<?php
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);
$queryuno="INSERT INTO phonelog (email,query_word,result_viewed) VALUES ('$to','$message_final', '$num_row')";  //LINE 5
$result=mysql_query($queryuno);
if ($result)
{
return;
}
mysql_close();
?>

[darkfreaks]

might try my suggestion above see what happens

 

also if i might say try to remove the @ symbols they only supress errors.

[rubing]

OK, I did it.

 

I get the same error message saying $num_row is not a defined variable.  And the even stranger part is that even though it's being reported it seems to work correctly when plugged into my Query  ???

 

and then will not be defined nor work when my query is 'next'

[darkfreaks]

did you get rid of the num_row line? it is not needed like i said earlier!

[rubing]

yes i changed numrows as follows:

 

modified numrows.inc

if ($message_final == 'next') 
	{
	$result=mysql_query("SELECT * FROM phonelog WHERE email = '$to' ORDER BY last_access DESC LIMIT 1");
	while ($row = mysql_fetch_assoc($result))
		{
		$message_final=$row["query_word"];
		$numrow=$row["result_viewed"];
		$numrow+=1;
		}
	}
else 
	{
	//gonna try to find how many rows there are before the users query
	$resnum=mysql_query("SELECT COUNT(*) FROM MusicEvents WHERE Date = '$today' AND Venue < '$message_final'");
	$num_row=mysql_result($resnum,0,"COUNT(*)");
	}

 

Now, when I execute my initial query i am delivered the proper result set (this queyr corresponds to the else statement above), but I receive the following error also: 

<b>Notice</b>:  Undefined variable:  message in <b>/home/rubing/lib/processtonight.php</b> on line <b>34</b><br />

 

Well it seems to be a couple of lines off in identifying errors.  ???  Here is line 34 again:

 

processtonight.php

while ($row = mysql_fetch_array($res))
	{
	if(!empty($row['HomeTown']) && !empty($row['myspace_address'])) 
		{
		$message .=$row['Band'] . " playing at " . $row['Venue'] . " \n" . $row['myspace_address'] . "\n";
		}

	elseif(!empty($row['HomeTown']) && empty($row['myspace_address'])) 
		{
		$message .=$row['Band'] . " playing at " . $row['Venue'] . " \n";
		}

	elseif(empty($row['HomeTown']) && !empty($row['myspace_address'])) 
		{
		$message .=$row['Band'] . " playing at " . $row['Venue'] . " \n" . $row['myspace_address'] . "\n";
		}
//**************LINE 34 IS HERE ***********//
	elseif(empty($row['HomeTown']) && empty($row['myspace_address'])) 
		{
		$message .=$row['Band'] . " playing at " . $row['Venue'] . "\n";
		}
	} 

$subject ='Tonight';
$headers = 'From: [email protected]' . "\r\n" . 'Reply-To: [email protected]' . "\r\n" . 'X-Mailer: PHP/' . phpversion();
mail($to, $subject, $message, $headers);
mysql_close();}
?>

 

When I send my next query:  'show next' (corresponds to if statement above).  I get a bunch of other errors saying that $numrow is not defined.    Here are the errors:

 

<b>Notice</b>:  Undefined variable:  num_row in <b>/home/rubing/lib/contact.inc</b> on line <b>5</b><br />

<br />

<b>Notice</b>:  Undefined variable:  num_row in <b>/home/rubing/lib/processtonight.php</b> on line <b>11</b><br />

<br />

<b>Warning</b>:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/home/rubing/lib/processtonight.php</b> on line <b>15</b><br />

<br />

<b>Notice</b>:  Undefined variable:  message in <b>/home/rubing/lib/processtonight.php</b> on line <b>40</b><br />

 

So, I guess the first thing to solve is that $message is undefined.  But really I don't understand how I can receive the answer to my query (contents of $message) if it is undefined

[rubing]

OK, I just solved the first error message by changing

 

$message .=

 

to

 

$message=

 

 

I am still getting the 2nd set of errors saying $numrow is undefined

[darkfreaks]

try using

MYSQL_NUM_ROWS function in php instead of mysql count function.

 

 

take the count function out and use mysql_num_rows like:

 

<?php

$numrow= mysql_num_rows($resnum);

if($numrow<=0) { echo"Error: No Previous Rows Listed!";

?>

[rubing]

:) :)  It was a stupid spelling mistake!!!! 

 

It's num_row, not numrow.  ooops.

 

Now, It works perfect!  THank you all so much!!!  :)

 

 

hmmm...is there a problem solved button somewhere???

[darkfreaks]

nope it hasnt been put back on since the site went down but they have more important stuff to do like rebuild the main site.