Need help getting the result of a query

[harleyhar]

I need some help with getting a mySql cross-tab query to work. The method requires two separate queries. The problem I'm having is that when I run the first query in phpMyAdmin I get exactly what I want. BUT, when I run it from the webpage I get - Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource. If I do

echo $result;

I get

1

.

 

Here's what I have;

 

My php function:

<?php
	function writecountpivot($tbl, $col, $y) {
	$sql = "SET @sqlmode = (SELECT @@sql_mode);";
	$sql .= "SET @@sql_mode='';";
	$sql .= "SELECT DISTINCT CONCAT( ',";
	$sql .= "SUM(IF(`".$col."` = \"',".mysql_real_escape_string($col).",'\",1,0)) AS `',`".$col."`,'`') AS countpivotarg ";
	$sql .= "FROM ".$tbl." WHERE `".$col."` IS NOT NULL;";
	$sql .= "SET @@sql_mode=@sqlmode;"; 
	$result = mysql_query($sql) or die ('Invalid query 12: ' . mysql_error());
	echo $sql;

	$sql2 = "SELECT `".$y."` ";
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$sql2 .= $row;
	}

	$sql2 .= ", COUNT(*) AS Total ";
	$sql2 .= "FROM ".$tbl;
	$sql2 .= " GROUP BY ".$y." WITH ROLLUP";

	return $sql2;		
}
?>

 

The first query returns this query string:

SET @sqlmode = (SELECT @@sql_mode);
SET @@sql_mode='';
SELECT DISTINCT CONCAT( ',SUM(IF(`Country` = "',Country,'",1,0)) AS `',`Country`,'`') AS countpivotarg FROM Sheet1 WHERE `Country` IS NOT NULL;
SET @@sql_mode=@sqlmode;

 

And here's how it runs in phpMyAdmin:

Your SQL query has been executed successfully

SET @sqlmode = ( SELECT @ @sql_mode ) ;# MySQL returned an empty result set (i.e. zero rows).
SET @ @sql_mode = '';# MySQL returned an empty result set (i.e. zero rows).
SELECT DISTINCT CONCAT( ',SUM(IF(`Country` = "', Country, '",1,0)) AS `', `Country` , '`' ) AS countpivotarg
FROM Sheet1
WHERE `Country` IS NOT NULL ;# Rows: 18
SET @ @sql_mode = @sqlmode ;# MySQL returned an empty result set (i.e. zero rows).

Showing rows 0 - 17 ( 18 total, Query took 0.0001 sec)

SELECT DISTINCT CONCAT( ',SUM(IF(`Country` = "', Country, '",1,0)) AS `', `Country` , '`' ) AS countpivotarg
FROM Sheet1
WHERE `Country` IS NOT NULL 

Output

countpivotarg
,SUM(IF(`Country` = "China",1,0)) AS `China`
,SUM(IF(`Country` = "Norway",1,0)) AS `Norway`
,SUM(IF(`Country` = "U.K.",1,0)) AS `U.K.`
,SUM(IF(`Country` = "Danmark",1,0)) AS `Danmark`
,SUM(IF(`Country` = "Romania",1,0)) AS `Romania`
,SUM(IF(`Country` = "USA",1,0)) AS `USA`
,SUM(IF(`Country` = "Canada",1,0)) AS `Canada`
,SUM(IF(`Country` = "Argentina",1,0)) AS `Argentin...
,SUM(IF(`Country` = "Brazil",1,0)) AS `Brazil`
,SUM(IF(`Country` = "Colombia",1,0)) AS `Colombia`
,SUM(IF(`Country` = "Germany",1,0)) AS `Germany`
,SUM(IF(`Country` = "India ",1,0)) AS `India `
,SUM(IF(`Country` = "Indonesia",1,0)) AS `Indonesi...
,SUM(IF(`Country` = "Libya",1,0)) AS `Libya`
,SUM(IF(`Country` = "Trinidad",1,0)) AS `Trinidad`
,SUM(IF(`Country` = "Turkey",1,0)) AS `Turkey`
,SUM(IF(`Country` = "UAE",1,0)) AS `UAE`

 

 

What I need is to get the output from the first query to build the second query, but I can't see to access it.

[fenway]

$result is an object, not a column value.

[harleyhar]

So then I should be able to read this using the mysqli methods?

[fenway]

Well, you'll have to fetch() something.

[harleyhar]

That begins my problem, there's nothing to fetch;

 

mysql_fetch_*anything*($result);

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

 

The only thing I can do is, echo $result;

 

1

 

[fenway]

Then you have an error --- check mysql_error().

[harleyhar]

The SQL is correct, as I get the expected result in my cPanel. I also have this line;

 

$result = mysql_query($sql) or die ('Invalid query 12: ' . mysql_error());

 

that doesn't throw an error right before I echo the actual SQL.

[mikosiko]

mysql api doesn't support muti-query sentences.... use mysqli api  or execute separates sentences

[harleyhar]

OK thanks, that makes sense. I'll try to recode everything for mysqli.

 

I read somewhere that mysql has been deprecated for mysqli, is this true?  Does it make any sense to begin to learn mysqli and start writing all new code with that?

 

Thanks again.

[fenway]

"learning" mysqli doesn't mean anything -- it's a wrapper class -- you shouldn't have to change anything.