dynamic table and field searches 2

max2474 · May 14, 2012

Hi. I am trying to save myself some coding. I think this is a php question rather than sql...

I have 5 tables in database called plan1, plan2 etc.. with each one having 9 columns (tot1, tot2, tot3, tot4, etc..)

I am wondering if there is a way to do something like the following :

PHP:

$c=1; 	
require("connectdb.php"); 
while ($c <= 9) 
{
   $u=$_SESSION[refs][$c-1];
   $result = mysql_query("SELECT * FROM [COLOR=red]plan.$_SESSION[plan][/color]
   WHERE userid = '$u' LIMIT 1");
   while($row = mysql_fetch_array($result))
   { 
      $_SESSION[rlvltot][$c-1]=[COLOR=red]$row['tot.$c'][/color]; 
   } 
   $c++;
} 
mysql_close($con);

where $_SESSION[plan] says which table to use and $c is which total to get.

This would save me writing out the same script 9 times.

Any hints you can give on how to achieve this would be great!

vacunita · May 14, 2012

Yup, that should totally work. IT does have an error, but other than that its completely valid.

Try this though:

Code:

while ($c <= 9)
{
   $u=$_SESSION[refs][$c-1];
   $result = mysql_query("SELECT * FROM plan." . $_SESSION['plan'] . " WHERE userid = '" . $u . "' LIMIT 1");
   $row = mysql_fetch_array($result);
   $_SESSION[rlvltot][$c-1]=$row['tot[COLOR=white red]'[/color].$c]; 
   $c++;
}

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

Borvik · May 15, 2012

Though - don't forget to validate $_SESSION['plan'] and $u to prevent against sql injection.

max2474 · May 15, 2012

I didnt believe that I would be even close to being right with how its done, so really pleased. thanks very much for the help so far.
retrieving the information is working great, though I am having an issue with writing the updated info back.

The script as follows :

PHP:

<?php

[COLOR=blue]---------- part one -----------[/color]
$f=0;
	require("connectdb.php");
while ($f <= 8)
{
	$v=$_SESSION[refs][$f];
	$rt=($_SESSION[rtokens][$f])+1;
	$rnt=($_SESSION[rnewtokens][$f])+1;
			mysql_query("UPDATE members SET tokens = '$rt', newtokens = '$rnt'
			WHERE userid = '$v' LIMIT 1");
	$f++;
}
	mysql_close($con);

[COLOR=blue]---------- part two -----------[/color]
$newplan="plan".$_SESSION[plan];
echo "newplan is ".$newplan;
$g=1;
	require("connectdb.php");
while ($g <= 9)
{
	$w=$_SESSION[refs][$g-1];
	$lnt="lvl".$g."tot";
	$lnn="lvl".$g."new";
	$ltu=($_SESSION[rlvltot][$g-1])+1;
	$lnu=($_SESSION[rlvlnew][$g-1])+1;
	$ptu=($_SESSION[rplantot][$g-1])+1;
	$pnu=($_SESSION[rplannew][$g-1])+1;
			mysql_query("UPDATE $newplan SET $lnt = '$ltu', $lnn = '$lnu', plantot = '$ptu', plannew = '$pnu'
			WHERE userid = '$w' LIMIT 1");
	$g++;
	
	echo "<br /> userid - w - is".$w;
	echo "lnt is ".$lnt;
	echo " lnn is ".$lnn;
	echo " ltu is ".$ltu;
	echo " lnu is ".$lnu;	
	echo " ptu is ".$ptu;
	echo " pnu is ".$pnu."<br />";	
}
	mysql_close($con);


?>

part one is updating the table fine, part two is not.

The echo statements were added just to ensure the variables were correct, which they are.

I have tried these 4 combinations as am sure that it is this line which is incorrect.

PHP:

"UPDATE $newplan SET $lnt = '$ltu', etc
"UPDATE $newplan SET '$lnt' = '$ltu', etc
"UPDATE '$newplan' SET $lnt = '$ltu', etc
"UPDATE '$newplan' SET '$lnt' = '$ltu',etc

I have noticed an oddity - if $newplan is set by the page to plan1, I do get some updated tables, but not when set to plan2 - plan5...

Just as a quick note - sq; injection is covered elsewhere in the script

Thanks for your assistance so far!

max2474 · May 15, 2012

Having turned into an sql question, I have moved the question there, with an update on my findings. Please have a look if you think you can help. Thanks

vacunita · May 15, 2012

The first thing I would do is make sure the queries all look correct once al values have been populated into them. Which is why its recommended to use a variable to pass to the mysql_query function, that you can print out.

Assuming the queries checkout, and can be correctly run directly on the databse via any GU or the command promtp then I would try to get some information from Mysql via its errors as:

Code:

mysql_query("UPDATE $newplan SET $lnt = '$ltu', $lnn = '$lnu', plantot = '$ptu', plannew = '$pnu'
			WHERE userid = '$w' LIMIT 1")[red]or die(mysql_error())[/red];

This should kill the script at the first error it finds and ouput the mysql error if any that may be stopping the update.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

jpadie · May 15, 2012

unless you know what $newplan looks like (i.e. you know what $_SESSION['plan'] is like), that's a dangerous route you are going down.

however ...

also definitely no single quotes around a table name. if there are any non-table name characters in the name (like spaces) then surround the $newplan variable with backticks.

Code:

"Update `$newplan` ...

the same with all column names.

all column values should be enquoted and cleansed (which I know you say you do elsewhere but it is good practice to do this at the point of the query OR to store sanitised variables with names of obvious nomenclature ($_SESSION['sanitised'][] etc).

if you are not using an abstraction layer, the easiest way to do this is as follows

Code:

$sql = vsprintf("
UPDATE `$newplan` 
SET     `$lnt` = '%s', 
        `$lnn` = '%s', 
        `plantot` = '%s', 
        `plannew` = '%s'
WHERE   `userid` = '%s'",  
        array_map (   
         'mysql_real_escape_string', 
         array( $ltu, $lnu, $ptu, $pnu, $w )
        )
);
mysql_query( $sql ) or die ("Error: " . mysql_error() . "<br/>\n $sql");

as vacunita says above, store the sql string in a variable so that you can print it easily in the event of an error.

max2474 · May 16, 2012

Solved!!

Many thanks to all of you that have given help. This is one chunky learning curve!

Using the or die(mysql_error()); found the error. Somehow (ahem) a typo had gotten into a table cloumn. Will have to remember that one for future issues.

Have taken a lot of the other tips on board too, have rewritten scripts about 3 times this week alone based upon advice ive seen..lol. keep doing that and i'll end up with the best programmed site ever...lol.

Really though, the help has been huge. Cant thank you enough.

Am sure it wont take too long before I'm back asking another - though hopefully a little while

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

dynamic table and field searches 2

max2474

Programmer

vacunita

Programmer

Borvik

Programmer

max2474

Programmer

max2474

Programmer

vacunita

Programmer

jpadie

Technical User

max2474

Programmer

Similar threads

Part and Inventory Search

Sponsor