I am new to php and I am trying to write the cross tab query for mysql using PHP. I constantly keep getting errors like PHP Parse error: syntax error, unexpected T_STRING in /home/sushvedula/cross.php on line 5
Kindly help.

This is my code.

<?php
$db->Connect('localhost','root','********','pack_00001');
for($i=0;$i<5;$i++)
for($j=0;$j<24;$j++)
select timestamp,
        group_concat(if(module=$i and block=$j,voltage,0)) as $v$i_$j
from block_voltages
group by timestamp;   
?>

Thanks in advance.

Firstly, all the code within the for block needs to be enclosed in curly braces. How is it supposed to know when the for loop starts and when it ends?

Secondly, lines 6-9 are a MySQL query but they're just sitting there pretending to be PHP code.

Thank you for the reply. But I am not sure how to make 6 look like mysql? I am sorry if this sounds like a silly question. I was under the impression that since the mysql query has not ended PHP must consider line 6 to be a part of the query.

Member Avatar for diafol

Are you trying to run queries or not? If so, use mysql_query(...). As Dani says, your SQL is just sitting there.

I still get the same error..I have added curly braces and also used mysql_query just before the select query.

Member Avatar for diafol

Show your new code. My crystal ball is cloudy! :)

The error no longer exists.Thank you so much..:) I did some changes to the variable names but I am still unable to get the correct value into $var. I guess the query is causing the problem.

<?php
$db=mysql_connect('localhost','root','********','pack_0001');
for($i=0;$i<5;$i++)
{
    for($j=0;$j<24;$j++)
    {
        $var='v'.$i.'_'.$j;
        mysql_query("select timestamp, (if(module=$i & block=$j,voltage,0)) as $var
        from block_voltages group by timestamp");
        echo "Var:$var";
    }
}
?>
Member Avatar for diafol

Yep query if a problem/ Hold on...

Timestamp doesn't seem to be a reserved word, but it may be safer to backtick it. thinks - I'm sure it was reserved. Hey ho, never mind.

You're grouping by timestamp, but this doesn't make any sense, as you're not aggregating any fields. Do you mean ORDER BY ??

You can't echo $Var because it doesn't exist. You need to extract the data from the resultset using mysql_fetch_array() or mysql_fetch_assoc(), BUT you haven't set one as you're running the query blind - you need to assign it to a variable.

The & is not a logical AND operator - for that you can use 'AND' or '&&'.

Are you really running 125 queries?? Seems very extreme.

Using $var in the SQL in this context doesn't make much sense either - just call it var if you need to.

The number of entries in the table is 300 million. Basically my problem is I have module values varying from 0-4 and block values from 0-23. All of this is stored in 1 table which increases the size of the table. The values are being recorded into the table once for every module every block..so for every timestamp I have 5*24 entries. So I want to have a crosstab using PHP that could reduce the size of my table.(I plan to have 24 column extra into this new table so essentially my number of rows reduce from 120 to 5) So in the for loop I run through all the blocks for all the modules to obtain the values respectively.

<?php
$db=mysql_connect('localhost','root','******','pack_0001');
for($i=0;$i<5;$i++)
{
for($j=0;$j<24;$j++)
{
$query=mysql_query("select timestamp, (if(module=$i & block=$j,voltage,0)) as ${'v'.$i.'_'.$j}
from block_voltages group by timestamp");
echo "${'v'.$i.'_'.$j}";
}
}
?>

However I get this error
PHP Notice: Undefined variable: v0_0 in /home/******/cross.php on line 9
PHP Notice: Undefined variable: v0_1 in /home/******/cross.php on line 9
PHP Notice: Undefined variable: v0_2 in /home/******/cross.php on line 9

Kindly help.

Member Avatar for diafol

I don't think you should store data in this form in a table. Crosstabs are queries, so they are based on the data that you've already got. You're not going to save any space like this. I can't see the problem anyway - all the data seems to be like tinyint or at the most int - so space-wise you should be OK.

I don't usually bother trying to create a crosstab query in MySQL - just dump the data into php arrays and run a couple of loops. Easier and more flexible (IMO) - MySQL solution sometimes need multiple queries or maybe a stored procedure if you want a one shot command.

I edited my previous post while you were posting - have a look.

I did have a look. But the time is a problem in my case. I have a table that has 300 million rows and counting. I was trying to insert values into a dummy table. Just a million values took me a good 7 hr(with indexing). When I use select,insert or delete due to the size of the table the query takes a lot longer. I was just experimenting to see if change of schema could be of any help. Could you shed some light on PHP arrays? This is my first time with PHP so forgive me for my silly doubts.

Member Avatar for diafol

300 million values!! Ho ho ho. Sounds extreme.
You may need to partition your table. You have a few options - usual partitions by RANGE or by HASH. This WILL take some time to do. What it essentially means is that you just search a subset of the whole table.

Here's a decent-ish link on the subject; http://chrismoos.com/2010/01/31/mysql-partitioning-tables-with-millions-of-rows/

The post has a couple of good links in it too.

An alternative would be to set up archive tables

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.