SELECT query using variable

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 36505 Views - Last Post: 14 November 2008 - 11:14 AM Rate Topic: -----

#1 deej_jinks  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-September 08

SELECT query using variable

Posted 29 September 2008 - 11:46 AM

Hi there

I've had a bit of a look around, and tried a few things, but I can't seem to get this to work... Can anyone help?

<?php
$con2 = mysql_connect("localhost","root","");
mysql_select_db("tester",$con2);

$na = $_GET["name"];
$idea = $_GET["idea"];

//echo $na;
//echo $idea;

$ideas = mysql_query("SELECT ideas FROM birthdays WHERE name = '$na'")or die(mysql_error());

echo $ideas;


It works fine if I use a fixed value instead of the variable..

I know it'll be something stupid, but I've been stuck on this for a while now :(

Any help much appreciated

Is This A Good Question/Topic? 0
  • +

Replies To: SELECT query using variable

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SELECT query using variable

Posted 29 September 2008 - 02:08 PM

First to solve your issue. Using the variable isn't working because the way you're including it in your query it is looking for a column named $na, which is assume doesn't exist. You need to concatenate the variable into your query, try this

<?php
$con2 = mysql_connect("localhost","root","");
mysql_select_db("tester",$con2) or die( "Unable to select database");;

$na = $_GET["name"];
$idea = $_GET["idea"];

//echo $na;
//echo $idea;

$ideas = mysql_query("SELECT ideas FROM birthdays WHERE name = '.$na.')or die(mysql_error());

echo $ideas;



Hope that helps :)

Second, this is more of a PHP question so I'm moving this to the PHP forum :)
Was This Post Helpful? 0
  • +
  • -

#3 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6106
  • View blog
  • Posts: 23,653
  • Joined: 23-August 08

Re: SELECT query using variable

Posted 29 September 2008 - 02:29 PM

You need to extract the data from the resultset.
$ideas = mysql_query("SELECT ideas FROM birthdays WHERE name = '.$na.')or die(mysql_error());
$i = 1;
while (($row = mysql_fetch_row) !== FALSE)
{
    echo "Idea $i: {$row[0]}\n");
    ++$i;
}

Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SELECT query using variable

Posted 29 September 2008 - 02:31 PM

LOL thanks, I didn't get that far with my help. I was trying to show him how to get the variable to work first. That was next, but have to write help code in between work code lol
Was This Post Helpful? 0
  • +
  • -

#5 deej_jinks  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-September 08

Re: SELECT query using variable

Posted 29 September 2008 - 02:45 PM

View PostPsychoCoder, on 29 Sep, 2008 - 02:08 PM, said:

First to solve your issue. Using the variable isn't working because the way you're including it in your query it is looking for a column named $na, which is assume doesn't exist. You need to concatenate the variable into your query, try this

<?php
$con2 = mysql_connect("localhost","root","");
mysql_select_db("tester",$con2) or die( "Unable to select database");;

$na = $_GET["name"];
$idea = $_GET["idea"];

//echo $na;
//echo $idea;

$ideas = mysql_query("SELECT ideas FROM birthdays WHERE name = '.$na.')or die(mysql_error());

echo $ideas;



Hope that helps :)

Second, this is more of a PHP question so I'm moving this to the PHP forum :)


Thanks for the response :)

I tried this though and it just returns "Resource id #3" for the variable $ideas :(

Unfortunately I'm such a noob I don't even know what that means :(
Was This Post Helpful? 0
  • +
  • -

#6 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3097
  • View blog
  • Posts: 10,883
  • Joined: 08-August 08

Re: SELECT query using variable

Posted 29 September 2008 - 02:50 PM

View PostJackOfAllTrades, on 29 Sep, 2008 - 02:29 PM, said:

$ideas = mysql_query("SELECT ideas FROM birthdays WHERE name = '.$na.')or die(mysql_error());
$i = 1;
while (($row = mysql_fetch_row) !== FALSE)
{
    echo "Idea $i: {$row[0]}\n");
    ++$i;
}

You don't need to check if it's not false:
while (($row = mysql_fetch_row) !== FALSE)

You can use:
while ($row = mysql_fetch_row)

;)

This post has been edited by CTphpnwb: 29 September 2008 - 02:50 PM

Was This Post Helpful? 0
  • +
  • -

#7 deej_jinks  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-September 08

Re: SELECT query using variable

Posted 29 September 2008 - 03:03 PM

Thanks for all the responses guys :)

I still can't get it to work, but I think I'm gonna sleep on it... Maybe it'll make more sense to me tomorrow...
Was This Post Helpful? 0
  • +
  • -

#8 DilutedImage  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 14
  • View blog
  • Posts: 646
  • Joined: 20-November 06

Re: SELECT query using variable

Posted 29 September 2008 - 07:56 PM

I'm not sure what you're trying to do with the $idea variable, but here's the code to echo the result(s) of your query (with a line break after each result row):

$con2 = mysql_connect("localhost","root","");
mysql_select_db("tester",$con2);

$na = $_GET["name"];

$results = mysql_query("SELECT ideas FROM birthdays WHERE name = '$na'")or die(mysql_error());

while($row = mysql_fetch_assoc($results)) {
	echo $row['ideas']; // echo the "ideas" of the current result row
	echo "<br />\n";  // echo a line break (in HTML and source)
}


This assumes that "ideas" and "name" are both fields within the "birthdays" table.

Using mysql_fetch_assoc() will give you an associative array of the result row, which will allow you to access the result data by their field names.


Was This Post Helpful? 0
  • +
  • -

#9 deej_jinks  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-September 08

Re: SELECT query using variable

Posted 30 September 2008 - 01:41 AM


So is the point then that the query results are a 1x1 array and not a field as I had hoped?

Is there an easy way to return a single field, or do I need to search my 1x1 array for the field?
(as I think is what people are suggesting - although I canīt get it to work...)

Oh- and as for what I was trying to do with the $ideas variable, I was trying to concatenate the new idea (passed through GET function) on the end to make a list, i.e.

$ideas = $ideas . ", " . $idea

Any help greatly appreciated :)
Was This Post Helpful? 0
  • +
  • -

#10 DilutedImage  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 14
  • View blog
  • Posts: 646
  • Joined: 20-November 06

Re: SELECT query using variable

Posted 30 September 2008 - 10:05 AM

Correct (at least in concept). MySQL queries return a resource. To access the data within the returned resource, special functions are used. Check out the PHP manual page for mysql_query, for a description of what to expect from the query. Basically though, here's the steps to getting data:
- Connect to the database
- Query the database (a result set or false will be returned)
- Get the result rows, one row at a time
- Get the desired data from the current row

It's also a good idea to do this when you're done:
mysql_free_result($result);
mysql_close();

This will free up the server's resources and close the database connection.

To dump your results into a comma-delimited string, just concatenate everything to a string (instead of echo), and replace the line break with a comma and space. Use if statements to determine if the comma is needed. Here's the revised code:
$con2 = mysql_connect("localhost","root","");
mysql_select_db("tester",$con2);

$na = $_GET["name"];
$idea = $_GET["idea"];
$ideas = '';

$results = mysql_query("SELECT ideas FROM birthdays WHERE name = '$na'")or die(mysql_error());
while($row = mysql_fetch_assoc($results)) {
	if($ideas != '') { $ideas .= ', '; }
	$ideas .= $row['ideas'];
}

mysql_free_result($result);
mysql_close();

if($idea != '') { $ideas .= ', ' . $idea; }
echo $ideas;

I don't have time to test this code right now, but it should all work fine. If it doesn't, then just look for a basic punctuation mistake. :)
Was This Post Helpful? 1
  • +
  • -

#11 deej_jinks  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-September 08

Re: SELECT query using variable

Posted 30 September 2008 - 11:32 AM

View PostDilutedImage, on 30 Sep, 2008 - 10:05 AM, said:

Correct (at least in concept). MySQL queries return a resource. To access the data within the returned resource, special functions are used. Check out the PHP manual page for mysql_query, for a description of what to expect from the query. Basically though, here's the steps to getting data:
- Connect to the database
- Query the database (a result set or false will be returned)
- Get the result rows, one row at a time
- Get the desired data from the current row

It's also a good idea to do this when you're done:
mysql_free_result($result);
mysql_close();

This will free up the server's resources and close the database connection.

To dump your results into a comma-delimited string, just concatenate everything to a string (instead of echo), and replace the line break with a comma and space. Use if statements to determine if the comma is needed. Here's the revised code:
$con2 = mysql_connect("localhost","root","");
mysql_select_db("tester",$con2);

$na = $_GET["name"];
$idea = $_GET["idea"];
$ideas = '';

$results = mysql_query("SELECT ideas FROM birthdays WHERE name = '$na'")or die(mysql_error());
while($row = mysql_fetch_assoc($results)) {
	if($ideas != '') { $ideas .= ', '; }
	$ideas .= $row['ideas'];
}

mysql_free_result($result);
mysql_close();

if($idea != '') { $ideas .= ', ' . $idea; }
echo $ideas;

I don't have time to test this code right now, but it should all work fine. If it doesn't, then just look for a basic punctuation mistake. :)


Well that worked perfectly, thanks!

No I just need to figure out in my head why ;)

Thanks again everyone for all the help...
Was This Post Helpful? 0
  • +
  • -

#12 DilutedImage  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 14
  • View blog
  • Posts: 646
  • Joined: 20-November 06

Re: SELECT query using variable

Posted 30 September 2008 - 11:39 AM

Happy to help.
Was This Post Helpful? 0
  • +
  • -

#13 ricferr  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 13-November 08

Re: SELECT query using variable

Posted 13 November 2008 - 12:31 PM

Hi all,

This topic has been of great help to me, as this is my first project with MySQL. Unfortunately, there's still something wrong with my code which is preventing it from working.

I have a simple one table MySQL database with 4 fields: numero, nome, email, resultado. Sorry about the Portuguese but I think its preferable, for the sake of better understanding my code, which follows:

$nome = $_POST["nomeVar"];
//$nome = "rf teste";
$email = '';

$results = mysql_query("SELECT email FROM colaboradores WHERE nome = '$nome'")or die(mysql_error());
while($row = mysql_fetch_assoc($results)) {
	if($email != '') { $email .= ', '; }
	$email .= $row['email'];
}

mysql_free_result($result);
mysql_close($link);

if($email != '') { $email .= ''; }
echo "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n";
echo "<colaboradores>\n";
echo "<email>" . $email . "</email>\n";
echo "</colaboradores>\n";

$sendTo = "ricardo.ferreira@pointoview.net";
$headers = "From: " . $nome;

$subject = "qemail.php - Resultado de " . $email;
$message = "O nosso colaborador, " . $nome . ";

mail($sendTo, $subject, $message, $headers);



The e-mail is just to check if the "nome" variable is reaching the PHP, which it is. "rf teste" is the name I have inserted in the table to perform all the testing.

I have a flash quizz game which populates a combobox with the names from the database (through PHP and XML). After answering the questions, each user selects his name and, by pressing a confirmation button, the DB should be queried for the corresponding e-mail address. The result is also inserted in the DB (no problem with that).

I just can't understand what's wrong with this. If I assign the "nome" in the PHP, using the commented 2nd line of code, the query is executed and I get the intended e-mail address back, in XML, ready to be read by flash.

When I send the variable from flash, it reaches PHP and is sent back to me via e-mail but, it fails to reach MySQL as the query isn't executed. Consequently, I get a null result in my flash application.

I hope I've clearly put it down for you. Thanks in advance

Regards

RF
Was This Post Helpful? 0
  • +
  • -

#14 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3097
  • View blog
  • Posts: 10,883
  • Joined: 08-August 08

Re: SELECT query using variable

Posted 13 November 2008 - 01:11 PM

This probably isn't the problem, but $result and $results are two different variables.
Was This Post Helpful? 0
  • +
  • -

#15 ricferr  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 13-November 08

Re: SELECT query using variable

Posted 13 November 2008 - 05:06 PM

View PostCTphpnwb, on 13 Nov, 2008 - 12:11 PM, said:

This probably isn't the problem, but $result and $results are two different variables.


You were twice right: those are two different variables, but meant to be the same and it wasn't the problem.

Any one has any clue?

Thanks

regards
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2