HOw to use a numeric value PHP variable in MySQL Query

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

30 Replies - 9159 Views - Last Post: 11 January 2013 - 10:32 PM Rate Topic: -----

#1 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

HOw to use a numeric value PHP variable in MySQL Query

Posted 06 January 2013 - 05:50 PM

Hello,
I am having trouble getting a query to execute correctly.

I am passing a numeric variable from a previous page using POST. The variable contains the number 3. Here is my code.

// Select the specific database
@mysql_select_db("soundrm_Website", $mySQL) or die("Unable to connect to database");

$Variable = $_POST['SpeakerMenu'];

$sql = "Select * from Sermons where Speaker_ID =$Variable";

//echo("<P>" . $sql . "</P>");

// execute query
$Speaker = mysql_query($sql);
if (!$Speaker)
{
	echo("<P>Error performing query: " .
		mysql_error() . "</P>");
	exit();
}
while ($row = mysql_fetch_array($Speaker))
{
	// Set Value
	echo("<P>" . $row["Title"] . "</P>"); 
}



When using Echo to check the Query I get this result: Select * from Sermons where Speaker_ID = 3

The query works if I replace the variable with 3 also.

When I execute with variable I get the following error: Error performing query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '

3
' at line 2

Is This A Good Question/Topic? 0
  • +

Replies To: HOw to use a numeric value PHP variable in MySQL Query

#2 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2990
  • Posts: 10,330
  • Joined: 08-August 08

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 06 January 2013 - 05:55 PM

Read this:
http://www.dreaminco...duction-to-pdo/
You should not be using outdated and insecure mysql* functions. Learn prepared statements. That will be easiter than learning to protect mysql* functions from hackers.
Was This Post Helpful? 2
  • +
  • -

#3 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 06 January 2013 - 06:53 PM

I will look into that. However I have no sensitive information in the tables and this is not for a secure website. All I really need to know is why the query is not working.

Thanks,
Kalor
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2990
  • Posts: 10,330
  • Joined: 08-August 08

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 06 January 2013 - 07:28 PM

Try it this way:
$sql = "Select * from Sermons where Speaker_ID ='".(int)$Variable."'";


Was This Post Helpful? 0
  • +
  • -

#5 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 06 January 2013 - 07:35 PM

Hi!

Can you please explain what the @ operator is doing in this code snippet? From what I know, it will suppress any error messages that woudl normally occur on that line. Make sure that it is connecting properly.

I agree with CTphpnwb, it could be an error of variable type. Try type casting, as suggested.

Best of luck!
Was This Post Helpful? 0
  • +
  • -

#6 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 06 January 2013 - 08:38 PM

Taking CTphpnwb's advice I have rewritten this code as follows:

$Variable = $_POST['SpeakerMenu'];

// connect to database server
$mySQL = "mysql:host=localhost;dbname=soundrm_Website";
$opt = array(
    // any occurring errors wil be thrown as PDOException
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    // an SQL command to execute when connecting
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);

try
{
	$pdo = new PDO($mySQL, "$user", "$pass", $opt)

	//create the sql
	//create a prepared statement
	$ps = $pdo->prepare("Select * from 'Sermons' Where 'Speaker_ID' =:ID");
	$ps->bindValue('ID', $Variable, PDO::PARAM_INT);
	$ps->execute();
	$ps->setFetchMode(PDO::FETCH_COLUMN, 2);
	
	$text = "";
	for each ($ps as $row)
	{
		$text .= $row . "<br>";
	}
	echo $text;
}
catch (Exception $e)
{
	echo '<P Class="error">Oops, we have encountered a problem, but we will deal with it. Promised.</P>';
	send_error_mail($e->getMessage());
}



I still get the same error.
some additional information: $_POST[SpeakerMenu] is a passed Parameter coming from a HTML Form Select Object. The parameter is populated with 3 as I have confirmed with echo. The db connection is verified and is fine.

Thanks,
Kalor
Was This Post Helpful? 1
  • +
  • -

#7 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2990
  • Posts: 10,330
  • Joined: 08-August 08

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 06 January 2013 - 08:54 PM

Is Speaker_ID a valid field in the database table?
Was This Post Helpful? 0
  • +
  • -

#8 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 06 January 2013 - 11:14 PM

Yes. The structure for Sermons is:

ID INT(10) PK AI,
Speaker_ID INT(10),
Date DATE,
Title VARCHAR(100),
Location VARCHAR(200)
Was This Post Helpful? 0
  • +
  • -

#9 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 07 January 2013 - 02:56 AM

i am pretty sure you did not follow 's advice to add (int).

$Variable coming from $_POST is defined as string, so you are trying to insert string in a integer field. use (int)$Variable.

i am pretty sure you did not follow 's advice to add (int).

$Variable coming from $_POST is defined as string, so you are trying to insert string in a integer field. use (int)$Variable.
Was This Post Helpful? 0
  • +
  • -

#10 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 07 January 2013 - 04:17 PM

Okay I now understand that Post always passes a string value. I was not aware of that before However I am still getting the same error

Parse error: syntax error, unexpected T_VARIABLE in /home/soundrm/public_html/AudioQuery.php on line 20.

The Code is as follows

<?php

$Variable = intval($_POST['SpeakerMenu']);

// connect to database server
$mySQL = "mysql:host=localhost;dbname=soundrm_Website";
$opt = array(
    // any occurring errors wil be thrown as PDOException
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    // an SQL command to execute when connecting
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);

try
{
	$pdo = new PDO($mySQL, "$webuser", "$pass", $opt)

	//create the sql
	//create a prepared statement
	$ps = $pdo->prepare("Select * from 'Sermons' Where 'Speaker_ID' = '"intval(:ID)"'");
	$ps->bindValue('ID', intval($Variable), PDO::PARAM_INT);
	$ps->execute();
	$ps->setFetchMode(PDO::FETCH_COLUMN, 3);
	
	$text = "";
	for each ($ps as $row)
	{
		$text .= $row . "<br>";
	}
	echo $text;
}
catch (Exception $e)
{
	echo '<P Class="error">Oops, we have encountered a problem, but we will deal with it. Promised.</P>';
	send_error_mail($e->getMessage());
}

?>


Was This Post Helpful? 0
  • +
  • -

#11 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 07 January 2013 - 04:25 PM

ehm... i did not try your code, because i am not at home. but there are more than 1 error. ;)

try this:
$ps = $pdo->prepare("Select * from 'Sermons' Where 'Speaker_ID' = :ID");
21
    $ps->bindValue(':ID', (int)$Variable);

This post has been edited by Anthonidas: 07 January 2013 - 04:26 PM

Was This Post Helpful? 0
  • +
  • -

#12 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 07 January 2013 - 04:33 PM

View PostAnthonidas, on 07 January 2013 - 04:25 PM, said:

ehm... i did not try your code, because i am not at home. but there are more than 1 error. ;)/>

try this:
$ps = $pdo->prepare("Select * from 'Sermons' Where 'Speaker_ID' = :ID");
21
    $ps->bindValue(':ID', (int)$Variable);



Tried it and get the same error. I did notice and correct that I had 'ID' instead of ':ID' in the Bind Value.
Was This Post Helpful? 0
  • +
  • -

#13 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 07 January 2013 - 04:40 PM

oh got it... you have used ' to enclose your table and field.

try using this sign: ´mytable´ and ´myfield´
or try using none of them.

This post has been edited by Anthonidas: 07 January 2013 - 04:41 PM

Was This Post Helpful? 1
  • +
  • -

#14 Kalor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 06-January 13

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 07 January 2013 - 04:53 PM

View PostAnthonidas, on 07 January 2013 - 04:40 PM, said:

oh got it... you have used ' to enclose your table and field.

try using this sign: ´mytable´ and ´myfield´
or try using none of them.



tried both and both got the same result as before. This is getting frustrating. :/
Was This Post Helpful? 0
  • +
  • -

#15 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: HOw to use a numeric value PHP variable in MySQL Query

Posted 07 January 2013 - 05:09 PM

i'm getting out of ideas...

perhaps it helps if you post your form, your PHP and your DB structure, so that we can test your script.
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3