Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
|
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 27th, 2003, 04:13 PM
Authorized User
 
Join Date: Aug 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using a variable in a SELECT WHERE query

Hello! I am trying to query a table (p) with 2 columns (id, pw) to find the corresponding "password" using an "id" input by a user.

Page 1: inputs $id.
Page 2 follows:
Code:
<?php
// Connect to db
$dbcnx = mysql_connect("localhost","username","password");
if (!$dbcnx) {
    die ("<p>Could not connect: " . mysql_error() . "</p>");
}

// Select db
$db_dbcnx = mysql_select_db("database", $dbcnx);
if (!$db_dbcnx) {
    die ("<p>Could not select db: " . mysql_error() . "</p>");
}

// Query info from table
$result = mysql_query('SELECT pw FROM p WHERE id=$id', $dbcnx);
if (!$result) {
    die ("<p>Error performing query: " . mysql_error() . "</p>");
}
else {
    echo ('<p>Your password is: '.$result.'.</p><p>Your password is case sensitive. Make sure you enter it correctly.</p>');
}

// Close db
mysql_close($dbcnx);
?>
I keep coming up with an error where $result displays as:
Code:
Error performing query: Unknown column '$id' in 'where clause'
If I change my query to read:
Code:
SELECT pw FROM p WHERE id=id
I get:
Code:
Your password is: Resource id #3. (etc.)
Thanks for your help!

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs
__________________
HollyAnn
aka Scottiegirl

\"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die.\" - Calvin, Calvin and Hobbs
 
Old October 27th, 2003, 07:55 PM
Registered User
 
Join Date: Oct 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The problem might stem to you using single quotes as that won't interpret the value of the variable. Try and use (") and see if that works.

Also, just as a tip, I would put your connection to you database at the top in an include so that if you change the password cause someone breaks into your box that you don't have to change your password on multiple pages.

 
Old October 28th, 2003, 10:31 AM
Authorized User
 
Join Date: Aug 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the advice, but it's still a no go. I tried:
Code:
$result = mysql_query("SELECT pw FROM p WHERE id=$id", $dbcnx);
and
Code:
$result = mysql_query("SELECT pw FROM p WHERE id=id", $dbcnx);
and both come up with the "Resource #3" error I quoted before.

Any other suggestions? Anyone?

Re: the include file, I'm working with a non-local server, and using the include file doesn't work for some reason. Still haven't figured it out. Working on it. Thanks for the heads up.


HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs
 
Old October 28th, 2003, 12:38 PM
Registered User
 
Join Date: Oct 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

try the query without the ", $dbcnx)" - you only need that if you have multiple mysql connections

 
Old October 28th, 2003, 01:11 PM
Registered User
 
Join Date: Oct 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also, did you put your result into a "mysql_fetch_array" or "mysql_fetch_assoc"

you have to have:

$result = mysql_query("here is your select");

while($row = mysql_fetch_assoc($result))
{
     print "My Password is: $row["name of your column for Password"];
}

You can't just straight print a result or it will come out with the "Resource id thing"

 
Old October 28th, 2003, 02:04 PM
Authorized User
 
Join Date: Aug 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

1. I just found out I need the ",$dbcnx" in the query because of the nature of the server I'm on. Thanks for the info though.
2. I put the result into both types of "mysql_fetch_"s for testing.
Code:
// Query info from table
$result = mysql_query("SELECT pw FROM p WHERE id=$id", $dbcnx);
if (!$result) {
    die ("<p>Error performing query: " . mysql_error() . "</p><p>Please <a href=\"mailto:me@mysite.com?subject=Password_Request\">email me</a> for your password.");
}
else {
    while($row = mysql_fetch_array($result)) {
        echo ("Your password is: $row['pw'].</p><p>Your password is case sensitive. Make sure you enter it correctly.</p>");
I get "Error performing query:" and a blank space where "Resource #3" used to be.

:(Grrr! This is so frustrating! (Relax! Breathing is a good thing!)

Any other programming suggestions???

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs
 
Old October 28th, 2003, 03:27 PM
Registered User
 
Join Date: Oct 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Do you have mysql admin for your server, if so I would print out what is in your select:

print "SELECT pw FROM p WHERE id=$id" which will output the actual syntax of your select and stick that in the query box in mysql admin. If that does not come back with an error then you know it is not your mysql syntax that is causing the problems. The reason I say that is if you mysql_error() is not printing anything out then your select might be ok.

 
Old October 29th, 2003, 07:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi,

Do it this way.

$result = mysql_query("SELECT pw FROM p WHERE id=" . $id, $dbcnx);

OR

$sqlquery='SELECT pw FROM p WHERE id=' . $id;
$result = mysql_query($sqlquery, $dbcnx);

Where "." acts as a concatenation operator.

Hope this solved your problem.

Cheers,

-Vijay G
 
Old March 2nd, 2004, 12:55 PM
Authorized User
 
Join Date: Aug 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry for the EXTREME delay in responding, Vijay. Your suggestion worked! Hurrah! Life is good...



HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs
 
Old April 15th, 2004, 01:45 PM
Authorized User
 
Join Date: Apr 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, I hope your still out there. I've been trying to solve a problem. And your code in this discussion come close to what I'm trying to do. Anyway, scottie girl could you help me? See, I'm trying to have my php page to where you click on an item and you veiw the general data from that item which I have successfully done. However, I cannot display the joint data from a row. To put it simple, I cannot veiw form a selected row. I think that was what you where trying to do. Anyway since you got yours to work I was just wandering if you could help me with mine.
Here's the code:
<?php
require_once ('mysql_connect.php'); // connecting to database.

// Im making the query.
$query = "SELECT CONCAT(Id) AS Id, (Name) AS Name, (Description) AS Des, DATE_FORMAT(Date, '%M %d, %Y') AS dr FROM AddTable1 WHERE Description IS NOT NULL ORDER BY Date ASC";
$result = @mysql_query ($query) OR die; // Run the query.
$num = mysql_num_rows ($result); // How many codes are there?

if ($num >0)
{ // If it ran ok

    // Display numbers
    echo "<p><big><b>These are the current number of entries: $num</b></big></p>";



    // Display headers
    echo '<table align="center" border="2" cellspacing="2" cellpadding="2">
    <tr><td align="left"><b>Id</b></td><td align="left"><b>Name</b></td><td align="left"><b>Category</b></td><td align="left"><b>Date</b></td><td></td><td align="left"><b>Click edit to update</b><td align="left"><b>Show the table</b></td></td></tr>';

    // Fetch the data
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
        echo "<form name=method='post' action='Show.php'>";
        echo "<tr><td align=\"left\">".stripslashes($row[0])."</td><td>$row[1]</td><td align=\"left\"><a href='edit.php?=".edit."'>$row[2]</a></td><td align=\"left\">$row[3]</td><td align=\"left\"><a href='edit.php?=".edit."'>$row[4]</a></td><td><a href='edit.php?=".edit."'>".edit."</a><br></td><td><input type='submit' name='Code' value='".$result[Code]."Show'></td></tr>\n";
        }

        echo '</table>';
        mysql_free_result ($result); // free up resources.

        } else { // If it didnt run OK.
            echo
            '<p>there are no codes in this query</p><p>' .mysql_error() . '</p>';
            }

mysql_close(); // Close the database.


include ('WSfooter.inc'); // footer included.
?>
here is the first level that works and when you click on one of the rows I want a proprietary display of all the rows related to that row.

so, here is the code that I'm having trouble with
<?php
// This page is to get the sql query to show the table being selected
// The title and header
$page_title = 'Show information';
include ('WSheader.inc');

// Variable Declaration with if statement

$c = $_Get['Code'];



// Query time - for selection of data
require_once ('mysql_connect.php'); // I am making a connection to database

// Variable Declaration with if statement

// This query should show the data from agenda section
$result = mysql_query("SELECT Code FROM AddTable2 WHERE Code=" . $c);

if (!$result) {
    die ("<p>Error performing query: " . mysql_error() . "</p>");
}
else {
    echo ('<p>Your password is: '.$result.'.</p><p>Your password is case sensitive. Make sure you enter it correctly.</p>');
}

mysql_close(); // Close the database.

include ('WSfooter.inc'); //Showing footer

?>

The browser keeps telling me that it is a problem with my SQL statment could you please help me






Similar Threads
Thread Thread Starter Forum Replies Last Post
SELECT VARIABLE Number of columns drani Oracle 4 September 21st, 2008 01:12 AM
passing javascript variable in xsl:value-of select eruditionist XSLT 2 September 19th, 2008 05:23 PM
Using variable as fieldname in SELECT statement elygp SQL Server 2000 3 April 26th, 2007 10:14 AM
Using a Variable in Select Into anubhav.kumar SQL Server 2000 2 May 26th, 2005 11:02 AM
Setting a variable using Select Case Adam ASP.NET 1.0 and 1.1 Basics 2 September 4th, 2004 02:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.