Topic: SQL Script does not run on CPanel only (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=28518" title="Pages that link to Topic: SQL Script does not run on CPanel only (Page 1 of 1)" rel="nofollow" >Topic: SQL Script does not run on CPanel only <span class="small">(Page 1 of 1)</span>\

 
Fikzy
Nervous Wreck (II) Inmate

From:
Insane since: Aug 2004

posted 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 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 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 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!



Post Reply
 
Your User Name:
Your Password:
Login Options:
 
Your Text:
Loading...
Options:


« BackwardsOnwards »

Show Forum Drop Down Menu