Topic awaiting preservation: SQL Script does not run on CPanel only $Pages that link to <a href="https://ozoneasylum.com/backlink?for=28518" title="Pages that link to Topic awaiting preservation: SQL Script does not run on CPanel only" rel="nofollow" >Topic awaiting preservation: SQL Script does not run on CPanel only\$

Author	Thread
Fikzy Nervous Wreck (II) Inmate From: Insane since: Aug 2004	posted 10-09-2006 18:54 I am runing MySQL 4.1.12 on my PC, PHP: 5.0.4 I Run The following SQL Queryon my Windows XP pc and it ran succesfully. SELECT Rating FROM ebmonitor eb1 WHERE IndicatorID = '101' AND regionID = '102' AND valID = ( SELECT MAX(valID) FROM ebmonitor eb2 WHERE eb1.IndicatorID=eb2.IndicatorID AND eb1.regionID=eb2.regionID ) Table Structure: ================= code: CREATE TABLE ebmonitor ( valID int(11) NOT NULL auto_increment, regionID int(11) NOT NULL default '0', IndicatorID int(11) NOT NULL default '0', Rating int(11) NOT NULL default '0', StatDate datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (valID) ) ; -- -- Dumping data for table `ebmonitor` -- INSERT INTO ebmonitor VALUES (1, 102, 101, 46, '2006-10-09 01:01:03'); INSERT INTO ebmonitor VALUES (2, 102, 102, 66, '2006-10-09 01:01:16'); INSERT INTO ebmonitor VALUES (3, 102, 103, 60, '2006-10-09 01:01:40'); INSERT INTO ebmonitor VALUES (4, 102, 104, 46, '2006-10-09 01:02:03'); INSERT INTO ebmonitor VALUES (5, 102, 105, 33, '2006-10-09 01:02:20'); INSERT INTO ebmonitor VALUES (6, 101, 101, 26, '2006-10-09 01:17:42'); INSERT INTO ebmonitor VALUES (7, 101, 102, 80, '2006-10-09 01:17:53'); But when I ran the same code on a CPanel Hosting with the folowing setup, I give the following error [even when I ran the query directly from the SQL query window in the phpMyAdmin too on cpanel.]; Error: ====================== Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/wanepnig/public_html/ebmonitor/emonitor.php on line 55 - MySQL - 4.0.27 - PHP version 4.4.3 See PHP Code Below; ========================== code: $query = "SELECT Rating". " FROM ebmonitor eb1". " WHERE IndicatorID = '$indicator' ". " AND regionID = '$region' ". " AND valID = (". " SELECT MAX(valID)". " FROM ebmonitor eb2". " WHERE eb1.IndicatorID=eb2.IndicatorID". " AND eb1.regionID=eb2.regionID)"; $result = mysql_query($query,$db); if(mysql_num_rows($result) > 0){... //Line 55 All help will be greatly valued F.O.O
butcher Paranoid (IV) Inmate From: New Jersey, USA Insane since: Oct 2000	posted 10-10-2006 03:43 You can't do sub queries (nested) in MySQL versions before 4.1. That's why it's complaining that supplied argument is not a valid MySQL result resource. For debugging things like this you might want to throw in a mysql_error() so you have an idea of what's causing the problem if it is indeed mysql related. $result = mysql_query($query,$db) or die(mysql_error()); - Butcher -
Fikzy Nervous Wreck (II) Inmate From: Insane since: Aug 2004	posted 10-10-2006 13:38 Any other way to achieve this same result without using subquery? I cant seem to find one my self and I need that query to free a hanging project. Butcher, any Idea? Me, Fikz-De-Fix!
Fikzy Nervous Wreck (II) Inmate From: Insane since: Aug 2004	posted 10-10-2006 15:52 This is how I resulted i solving the challenge... code: $tempQuery = "SELECT MAX(valID) as valID FROM ebmonitor where IndicatorID='$indicator' AND regionID='$region'"; $result = mysql_query($tempQuery,$db) or die(mysql_error()); if(mysql_num_rows($result) > 0){ $queryVal = mysql_result($result,0,"valID"); $query = "SELECT Rating". " FROM ebmonitor eb1". " WHERE IndicatorID = '$indicator'". " AND RegionID = '$region'". " AND ValID = '$queryVal'"; $result = mysql_query($query,$db); if(mysql_num_rows($result) > 0){ Me, Fikz-De-Fix!

Topic awaiting preservation: SQL Script does not run on CPanel only\$

Author

Thread

Fikzy
Nervous Wreck (II) Inmate

From:
Insane since: Aug 2004

posted 10-09-2006 18:54

I am runing MySQL 4.1.12 on my PC, PHP: 5.0.4

I Run The following SQL Queryon my Windows XP pc and it ran succesfully.

SELECT Rating FROM ebmonitor eb1
WHERE IndicatorID = '101' AND regionID = '102'
AND valID = (
SELECT MAX(valID)
FROM ebmonitor eb2
WHERE eb1.IndicatorID=eb2.IndicatorID AND eb1.regionID=eb2.regionID
)

Table Structure:
=================

code:

CREATE TABLE ebmonitor (
  valID int(11) NOT NULL auto_increment,
  regionID int(11) NOT NULL default '0',
  IndicatorID int(11) NOT NULL default '0',
  Rating int(11) NOT NULL default '0',
  StatDate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (valID)
) ;

-- 
-- Dumping data for table `ebmonitor`
-- 

INSERT INTO ebmonitor VALUES (1, 102, 101, 46, '2006-10-09 01:01:03');
INSERT INTO ebmonitor VALUES (2, 102, 102, 66, '2006-10-09 01:01:16');
INSERT INTO ebmonitor VALUES (3, 102, 103, 60, '2006-10-09 01:01:40');
INSERT INTO ebmonitor VALUES (4, 102, 104, 46, '2006-10-09 01:02:03');
INSERT INTO ebmonitor VALUES (5, 102, 105, 33, '2006-10-09 01:02:20');
INSERT INTO ebmonitor VALUES (6, 101, 101, 26, '2006-10-09 01:17:42');
INSERT INTO ebmonitor VALUES (7, 101, 102, 80, '2006-10-09 01:17:53');

But when I ran the same code on a CPanel Hosting with the folowing setup, I give the following error [even when I ran the query directly from the SQL query window in the phpMyAdmin too on cpanel.];

Error:
======================
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/wanepnig/public_html/ebmonitor/emonitor.php on line 55
- MySQL - 4.0.27
- PHP version 4.4.3

See PHP Code Below;
==========================

code:

$query = "SELECT Rating".
		            " FROM ebmonitor eb1".
		            " WHERE IndicatorID = '$indicator' ".
		            " AND regionID = '$region' ".
		            " AND valID = (".
		            " SELECT MAX(valID)". 
		            " FROM ebmonitor eb2".
		            " WHERE eb1.IndicatorID=eb2.IndicatorID".
		            " AND eb1.regionID=eb2.regionID)";
	$result = mysql_query($query,$db);
	if(mysql_num_rows($result) > 0){... //Line 55

All help will be greatly valued

F.O.O

butcher
Paranoid (IV) Inmate

From: New Jersey, USA
Insane since: Oct 2000

posted 10-10-2006 03:43

You can't do sub queries (nested) in MySQL versions before 4.1. That's why it's complaining that supplied argument is not a valid MySQL result resource. For debugging things like this you might want to throw in a mysql_error() so you have an idea of what's causing the problem if it is indeed mysql related.

$result = mysql_query($query,$db) or die(mysql_error());

- Butcher -

Fikzy
Nervous Wreck (II) Inmate

From:
Insane since: Aug 2004

posted 10-10-2006 13:38

Any other way to achieve this same result without using subquery? I cant seem to find one my self and I need that query to free a hanging project.
Butcher, any Idea?

Me,
Fikz-De-Fix!

Fikzy
Nervous Wreck (II) Inmate

From:
Insane since: Aug 2004

posted 10-10-2006 15:52

This is how I resulted i solving the challenge...

code:

$tempQuery = "SELECT MAX(valID) as valID FROM ebmonitor where IndicatorID='$indicator' AND regionID='$region'";
		$result = mysql_query($tempQuery,$db) or die(mysql_error());
	if(mysql_num_rows($result) > 0){
		$queryVal = mysql_result($result,0,"valID");
		$query = "SELECT Rating".
					" FROM ebmonitor eb1".
					" WHERE IndicatorID = '$indicator'".
					" AND RegionID = '$region'".
					" AND ValID = '$queryVal'";
		$result = mysql_query($query,$db);
		if(mysql_num_rows($result) > 0){

Me,
Fikz-De-Fix!