PHP Variable Inside MySQL Query 1

[Kharas]

I'm trying to introduce a $variable inside a MySQL query.

In a previous thread:

http://www.tek-tips.com/viewthread.cfm?qid=376634

:

1-sleipnir214 suggested the usage of a global variable, and provided a link with inclusions of the global var into normal functions().

2-LTeeple suggested concatenating the variable outside of the SQL Instruction.

I've tried both methods and so far I've had no success. Where within the MySQL query do you place the global var without altering it?

As for the concatenation, My SQL instruction is longer so using concatenation results in instrunction-concatenated var-instruction, which returns an error.

Here is my Query along with the defined var on top:

// Var definition, Value comes from the an URL variable "tipo"
$tipo = $_GET['tipo'];

// Query with the inclusion of $tipo inside the SQL instruction
mysql_select_db($database_conn_siitto, $conn_siitto);
$query_lista_main = sprintf("SELECT * FROM tbl_montac, tbl_marca WHERE tbl_marca.marca_ID = tbl_montac.marca AND tipo = $tipo ORDER BY mont_ID ASC", GetSQLValueString($colname_lista_main, "int"));
$lista_main = mysql_query($query_lista_main, $conn_siitto) or die(mysql_error());
$row_lista_main = mysql_fetch_assoc($lista_main);
$totalRows_lista_main = mysql_num_rows($lista_main);

 

[sleipnir214]

I'm having difficulty understanding your question.

My advice from the other thread does not apply here. The code you posted does not include a user-defined function.

What are you trying to do in this line:

$query_lista_main = sprintf("SELECT * FROM tbl_montac, tbl_marca WHERE tbl_marca.marca_ID = tbl_montac.marca AND tipo = $tipo ORDER BY mont_ID ASC", GetSQLValueString($colname_lista_main, "int"));

?

Why are you using sprintf()?


Want the best answers? Ask the best questions! TANSTAAFL!

 

[Kharas]

Ok, I'll try to rephrase:

I am building an inventory of used equipment. The first page shows the 4 main categories: attachments, batteries, chargers and forklifts.

Right now I’m working on the forklifts section, which is the most complex.
Now, I have the forklifts split into 2 main categories: Electric & Combustion. Each of these has its own types:

Electric: Counterbalance, Rider Pallet, Rider Reach, Stockpicker, Tow Tractor, Turret, Walkie Pallet, Walkie Stacker

Combustion: Pneumatic, Cushion

----

Anyhow, when you click in any of the forklift types, such as ‘Counterbalance’ for example, you are taken to a product listing page that gives you a listing with basic info of the products that fall into such type, like ID, Manufacturer, Year and Price.

When you click on any of the individual ID’s, you are taken to that specific product’s detail page that now shows the full specs.

I already succeeded at doing this, but having a product listing page per type. My goal right now is to simply have one for Electric forklifts and another for Combustion.

The way it was working before was the following: Each forklift type page had its own MySQL query looking into the database tables. The filtering was done by choosing only elements that met the FORKLIFT TYPE specification, just like ‘Counterbalance’ for example.

The SQL instruction pulled the ID, Manufacturer, Year and Price from the main table ‘tbl_montac’ Where the forklift type (contained within tbl_montac as well) would match the desired criteria.

But such instruction was static, for ‘the desired criteria’ was manually specified on the code.

What I wanna do now is have a $variable inside the SQL instruction. And that $variable will be equal to an URL variable from the previous page. Picture this:

The user clicks on the category ‘Counterbalanced’ whose link termination is ?type=1.
The URL variable is type, and the value is now 1. I specify my $variable to be equal to my URL variable ‘type’.

Now I insert the $variable into my SQL instruction in the attempt of making it dynamic, regardless of the type value being 1,2,3,4,5,6,7 or 8.
So, I’ll give you the same query but with English table names so you get a clearer idea:

$variable = $_GET['type'];

SELECT * FROM forklift_table, manufacturer_table WHERE manufacturer_table.manufacturer_ID = forklift_table.manufacturer AND type = $variable ORDER BY forklift_ID ASC"

So, first of all:

1- Is it doable to introduce a variable inside an SQL statement?
2- If so, how?

Thanks in advance

 

[sleipnir214]

An SQL query is nothing more than a string and there are many ways of using a variable within a string. I prefer using contatenation with the "." concatenation operator:

$a = 'cat';
$b = 'Dog: ' . $a . ' :dog';


You could use sprintf(), too, but you are using the function incorrectly in the code you posted. An invocation that actually makes use of sprintf() is something like:

$a = 'cat';
$b = sprintf ('Dog: %s :dog', $a);

Read up on the sprintf() function




Note: In all code examples, $b will end up containing the string "Dog: cat :dog".



Want the best answers? Ask the best questions! TANSTAAFL!

 

[Kharas]

Sorry for the late reply, but my host startlogic apparently went through maintenance and everything was unaccesible.

I tried it once the service was restored and it worked. Thanks a million.

<?php require_once('Connections/conn_siitto.php'); ?>
<?php

$tipo = $_GET['tipo'];
mysql_select_db($database_conn_siitto, $conn_siitto);
$query_lista_main = "SELECT * FROM tbl_montac, tbl_marca WHERE tbl_marca.marca_ID = tbl_montac.marca AND tipo = ".$tipo." ORDER BY mont_ID ASC";
$lista_main = mysql_query($query_lista_main, $conn_siitto) or die(mysql_error());
$row_lista_main = mysql_fetch_assoc($lista_main);
$totalRows_lista_main = mysql_num_rows($lista_main);

?>

 

[JBlair]

One more note, because you are using double-quotes (") You shouldn't even need to concatenate.

A variable inside double-quotes is automatically rendered at run-time.

For example:
$a = 'cat';
$b = "dog: $a :dog";
$c = 'dog: '.$a.' :dog';
echo $b; // Should display "dog: cat :dog"
echo $c; // Should display exactly the same as the previous line.

You need to concatenate if you use single quotes('), as per Sleipnir214's examples.

 

[Kharas]

I tried that before JBlair, but it returns me "There's an error with your syntax at line..." prompt.

 

[clodhoppers18]

I am trying to execute something like he is, however I havent had my problem resolved with the solution that you gave. Please note I am new to PHP so there very well maybe another error that is causing it to go whack.

Basically I am pulling a list of flights from a database and running them through an "application" (if I could call it such a thing) that is later down the page. This list of flights that will be run through will only be ran once, then will never need to go through it again. So I devised a way to have the script start off by calling the last modified time from a different table into the query which will execute and pull in flights that have not been run. The flights have a timestamp on each one, and that is what I am using to pull them out by(assuming everything works correctly).

I have a script at the bottom of the page which updates the last modified time that is in the fspay table with the id of 1, thereby allowing me to call that time in the next time the script is run.

Here it is Before:
37|$lastrun = 'SELECT * FROM `fspay` WHERE CONVERT( `fspay`.`id` USING utf8 ) = \'1\' LIMIT 1 ;';
38|$lastquery=@mysql_query($lastrun);
39|$lasttime= mysql_fetch_array( $lastquery , time);
40|$querystamp = 'SELECT * FROM `flights` WHERE `datestamp` & gt ; = \ '$lasttime' \' LIMIT 0, 30 ';
41|$result=@mysql_query($querystamp);if(!$result){echo "SQL Error - ".mysql_error();return;}

Here it is after I attempted to make the adjustments as noted here in this topic:
37|$lastrun = 'SELECT * FROM `fspay` WHERE CONVERT( `fspay`.`id` USING utf8 ) = \'1\' LIMIT 1 ;';
38|$lastquery = @mysql_query($lastrun);
39|$lasttime = mysql_fetch_array($lastquery, MYSQL_ASSOC);
40|$oldtime = $_GET['time']
41|$querystamp = "SELECT * FROM `flights` WHERE `datestamp` & gt ; = \ '".$oldtime."\' LIMIT 0, 30 ";
42|$result=@mysql_query($querystamp);if(!$result){echo "SQL Error - ".mysql_error();return;}

Here is the error code I still get:
Parse error: syntax error, unexpected T_VARIABLE in /home/user/domains/mydomain.com/public_html/1.php on line 41

 

[jpadie]

clodhoppers18 - please start a separate thread for your problem. if there is a linkage to this thread then say so in the thread that you start.