HOw to use a numeric value PHP variable in MySQL Query

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

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

#16 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

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

Posted 07 January 2013 - 05:27 PM

Is this still a "Syntax error" you are getting? If so, take a closer look at line #26 in the last snippet you posted. for each should be a single word: foreach.
Was This Post Helpful? 1
  • +
  • -

#17 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 - 08:27 PM

Sure the form code is below.

<P> Speaker </P>
<BLOCKQUOTE>

<form name = "speaker" action = "AudioQuery.php" method = "POST">
<div align = "left">
<select name = "SpeakerMenu">

<?php

// execute query
$Speaker = mysql_query("Select * from Speakers");
if (!$Speaker)
{
	echo("<P>Error performing query: " .
		mysql_error() . "</P>");
	exit();
}

// load query results into array
while ($row = mysql_fetch_array($Speaker))
{
?>
<Option Value = "
<?php
	// Set Value
	echo("<P>" . $row["ID"] . "</P>"); 
?> 
">
<?php
	// Set Alias
	echo("<P>" . $row["Name"] . "</P>"); 
?>

</option>
<?php
}
?>
</option>
</select>
<input Type = "submit" name = "submit" value = "Submit" Align = "left"/>
<div>
</form>



The php you have above and the db structure consists of two tables at the moment.

The tables are Speakers

ID INT(10)PK AI
Name Varchar(100)

and Sermons

ID INT(10) PK AI
Speaker_ID INT(10)
Date Date
Title Varchar(150)
Location Varchar(200)
Was This Post Helpful? 0
  • +
  • -

#18 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 08 January 2013 - 01:34 AM

did you notice that on line 16 you forgot a semicolon?

$pdo = new PDO($mySQL, "$webuser", "$pass", $opt); <-- IMPORTANT!

and why do you use quotes for user and password, if they are variables?!

This post has been edited by Anthonidas: 08 January 2013 - 01:40 AM

Was This Post Helpful? 1
  • +
  • -

#19 raghav.naganathan  Icon User is offline

  • Perfectly Squared ;)
  • member icon

Reputation: 408
  • View blog
  • Posts: 1,440
  • Joined: 14-September 12

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

Posted 08 January 2013 - 01:37 AM

Also your <div> at the end of the program should be a </div>

regards,
Raghav
Was This Post Helpful? 1
  • +
  • -

#20 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 08 January 2013 - 01:42 AM

i tested your script and after some modification it works on my testing environnement:

$Variable = 1;

// 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, "myuser", "mypassword", $opt);
	// or you can use
	// $pdo = new PDO($mySQL, $user, $mypassword, $opt);

	//create the sql
	//create a prepared statement
	$ps = $pdo->prepare("Select * from `Sermons` Where `Speaker_ID` = :ID");
    $ps->bindValue(':ID', (int)$Variable);
	$ps->execute();
	$ps->setFetchMode(PDO::FETCH_COLUMN, 2);
	
	$text = "";
	foreach ($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>'.$e->getMessage();
	//send_error_mail($e->getMessage());
}

This post has been edited by Anthonidas: 08 January 2013 - 01:42 AM

Was This Post Helpful? 1
  • +
  • -

#21 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 08 January 2013 - 09:50 PM

Thank You Anthonidas and everyone else. I am not getting an error or anything anymore. Unfortunately I am not getting any results either. However I will work on that one myself and come back if I can't find an answer.
Was This Post Helpful? 0
  • +
  • -

#22 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 09 January 2013 - 06:16 AM

try to debug your code... try to find where "errors" could be by outputting the information you have. For example try to execute the query without the "WHERE"-clause and see if you get something. I mean to just selecet everything from sermons:
$ps = $pdo->prepare("Select * from `Sermons`");
    $ps->execute();


And remember, if a post was helpful, click on the green plus-icon ;)
Was This Post Helpful? 0
  • +
  • -

#23 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 09 January 2013 - 09:01 PM

okay I need to convert a post variable to an integer. But the conversion seems to be setting the value to 0

$variable = $_POST['SpeakerMenu'];

Echo $variable;

Echo (int)$variable;

Echo intval($variable);



Result is below

1
0
0
Was This Post Helpful? 0
  • +
  • -

#24 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

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

Posted 09 January 2013 - 10:48 PM

What does this print?
var_dump($_POST['SpeakerMenu']);


The var_dump function prints a lot more details about the value than echo does, which may show you why PHP is not converting it the way you are expecting it to.
Was This Post Helpful? 1
  • +
  • -

#25 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 09 January 2013 - 11:44 PM

View PostAtli, on 09 January 2013 - 10:48 PM, said:

What does this print?
var_dump($_POST['SpeakerMenu']);


The var_dump function prints a lot more details about the value than echo does, which may show you why PHP is not converting it the way you are expecting it to.



The result of var_dump shows:

string(13) "

1
"

This means that the String can be 13 characters but only contains 1, correct?
Was This Post Helpful? 0
  • +
  • -

#26 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

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

Posted 10 January 2013 - 01:57 AM

No, that means that the string is thirteen characters. var_dump is telling you exactly what the value is, not what it can be. This isn't like VARCHAR(13) in MySQL.

What version of PHP are you using, and on which OS?

Notice how the 1 is not immediately following the first double-quote? It appears that there are two new-lines in front of it. PHP should have no trouble converting that string to a number (it's pretty smart when it comes to correcting little problems like that). However, it is possible that the junk in front of the "1" is messing with the conversion on some systems. To remove that possibility, use trim on the input first.
$strVal = trim($_POST["SpeakerMenu"]);

$intVal = (int) $strVal;

var_dump($intVal);


That should print your number.
Was This Post Helpful? 2
  • +
  • -

#27 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 10 January 2013 - 09:08 PM

okay changed my code as Atli suggested.

$Variable = trim($_POST['SpeakerMenu']);
$intVal = (int)$Varialbe;
$dump = var_dump($Variable);
$dump = var_dump($intVal);



Results:

string(8) "

1
" int(0)


Since Trim by definition removes white space left of the fist character and right of the last character I assu,e this means that there is also space after the first character and before the last and I will need to parse out the string so that only the number value remains. What method would you recommend to do that or am I completely wrong here?
Was This Post Helpful? 0
  • +
  • -

#28 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

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

Posted 11 January 2013 - 01:11 AM

You spelled $Variable wrong when you converted it into the int.

If the trim function is not having an effect on the value, there is something even more odd happening there.

To start with, why is the "SpeakerMenu" variable surrounded by junk? What, in the HTML, is causing it to come through to PHP in this state?
Was This Post Helpful? 0
  • +
  • -

#29 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 11 January 2013 - 06:00 PM

As far as I can tell. The form code in HTML is good.

<form name = "speaker" action = "AudioQuery.php" method = "POST">
<div align = "left">
<select name = "SpeakerMenu">

<?php

// execute query
$Speaker = mysql_query("Select * from Speakers");
if (!$Speaker)
{
	echo("<P>Error performing query: " .
		mysql_error() . "</P>");
	exit();
}

// load query results into array
while ($row = mysql_fetch_array($Speaker))
{
?>
<Option Value = "
<?php
	// Set Value
	echo("<P>" . $row["ID"] . "</P>");
?>
">
<?php
	// Set Alias
	echo("<P>" . $row["Name"] . "</P>"); 
?>
</Option>
<?php
}
?>
</select>
<input Type = "submit" name = "submit" value = "Submit" Align = "left"/>
</div>
</form>


Was This Post Helpful? 0
  • +
  • -

#30 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

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

Posted 11 January 2013 - 07:22 PM

Nope, it's not. Take a closer look at this part:
<Option Value = "
<?php
	// Set Value
	echo("<P>" . $row["ID"] . "</P>");
?>
">


What is the <P> tag doing in the value? Is the value supposed to be the string <P>X<</P>? If not, then the <P> tag has no business being there.

The end result of that code would look something like:
<Option Value = "
<P>1</P>
">
<Option Value = "
<P>2</P>
">
<Option Value = "
<P>3</P>
">


This explains why the output and the conversion didn't make sense. When you were var_dump-ing the value from PHP to debug it, the <P> tag was printed to the browser, and it displayed it as a paragraph rather than show it.

What you were really getting from the form was:
string(13) "

<P>1</P>
"int(0) 


And even after you trimmed the new-lines away, it would just turn into:
string(8) "<P>1</P>"
int(0) 



This would cause the conversion to fail, because the value is, in fact, not a number, but a HTML string.

To fix this, you would want to remove both the <P> and the new-lines from that code. Make sure that only the value is printed into the value attribute; no HTML tags and no new-lines.
Was This Post Helpful? 2
  • +
  • -

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