Hello. I am new to PHP + MySQL. I appreciate if any friends at this forum can help me about a problem at retreiving data from MySQL.

My environment:

Windows 2000 Pro SP4
Zend Studio Server 3.0.0
PHP 4.3.2
MySQL 4.0.20a

MySQL includes:

database: database_user
table: table_password

There are two columns inside table_password:
user_name
user_password

I want to query the corresponding user_name in table_password by submitting user_password.

My codes are:

<?php
//Connect to the databse with the following 3 pieces of data defined as constants.
define ("DB_HOST", "localhost");
define ("DB_USER", "root");
define ("DB_PASSWORD", "password");

$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) or die ("Fails to connect to MySQL.");

//Select the database.
$database = "database_user";
$dbs = mysql_select_db ($database, $dbc) or die ("Failure to select database ($database).");

//Return the corresponding user name after looking up the code in the table_password.

$sql = 'SELECT user_name '
. ' FROM table_password '
. ' WHERE 1 AND user_password '
. ' LIKE \'$code\' LIMIT 0, 30';

$query_psq_user_id = mysql_query ("$sql") or die ("Failure to query table (password).");

?>

I receive Failure to query table (password) each time I execute the codes.

However if I remove . ' WHERE 1 AND user_password ' . ' LIKE \'$code\' LIMIT 0, 30' from $msql, the codes executes just fine.
How should I correct the codes?

In fact, my ultimate purpose is to use the user_name to query some other data in another table. Is there a shortcut other than to retreive the user_name first and to query by user_name later?

Thanks in advance.

    It's a problem of using single quotes. Any variables inside of single quotes will not be rendered as variables, but will be taken literal. So your sql query is literally looking for LIKE '$code'. Use double quotes around your query string, and you won't have that problem.

      That is for the outer quotes correct? ?Because the following works just fine.

      $sql = "select id from users where something = '$someVar'";

        Yes, the outer quotes. PHP takes anything in single quotes as literal.

          I see thats interesting. Thanks Lord

            Thanks so much for the quick help. I can't believe that users of this forum is so enthusiatic that my thread receives several follow-ups in such a short period of time.

            I modified that $sql part from:

            $sql = 'SELECT user_name '
            . ' FROM table_password '
            . ' WHERE 1 AND user_password '
            . ' LIKE \'$code\' LIMIT 0, 30';

            to:

            $sql = "SELECT user_name "
            . " FROM table_password "
            . " WHERE 1 AND user_password "
            . " LIKE \"$code\" LIMIT 0, 30";

            But I keep on receiving "Failure to query table (password)." Is there something I missed?

            Still, if I remove that WHERE1 AND...part, the script runs fine. However I need that WHERE condition.

            Thanks in advance.

              Well, couple things. First, why use LIKE with no wildcards? It's looking for the password to match it, and if that's the case, the = would be better. Here's two options, one with wildcards, one without. the WHERE 1 isn't needed either.

              # no wildcards
$sql = "SELECT user_name
          FROM table_password
         WHERE user_password = '".$code."'
         LIMIT 0, 30";

# with wildcards
$sql = "SELECT user_name
          FROM table_password
         WHERE user_password LIKE '%".$code."%'
         LIMIT 0, 30";

# use mysql_error to get error details
$res = mysql_query($sql) or die(mysql_error());

                Thanks LordShryku.

                I tried both suggestions but I get the same "Failure to query table (password)". That means something still goes wrong in that mysql_query ($sql) part.

                Is it possible that has something to do with my Apache 1.3 / PHP 4.3.2 / MySQL 4.0.20 / Zend Studio Server 3.0.0?

                  Not likely. Not unless mysql isn't running or isn't installed. What did mysql_error output?

                    Thanks LordShryku

                    Since I put the "or die" section after mysql_query ("$sql"), it outputs "Failure to query table (password)."

                    $query_psq_user_id = mysql_query ("$sql") or die ("Failure to query table (password).");

                    It seems to have nothing wrong in elsewhere but in this mysql_query part.

                    The MySQL data retrieval should be easy, right? What goes wrong...why I am stuck at here...-_- ?

                      Use or die(mysql_error()); while you're debugging. This will give you the actual error instead of your custom error message.

                        Thanks LordShryku.

                        Now, after I submit "abcdef" as the password (=$code), it displays:

                        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 'user_password = 'abcdef' LIMIT 0, 30' at line 1

                        My MySQL version is 4.0.20a-nt as displayed in phpMyAdmin 2.5.7.
                        By the way, I have no problem querying the database using phpMyAdmin. And that part of code:

                        $sql = 'SELECT user_name '
                        . ' FROM table_password '
                        . ' WHERE 1 AND user_password '
                        . ' LIKE \'$code\' LIMIT 0, 30';

                        was directly generated by and copied from phpMyAdmin.

                          Is it because the syntax changed in MySQL 4.0.20a (= 4.0.20a-nt in Windows 2000)?

                          Where can I check the right syntax? Or is it really related to the syntax?

                            What aren't you telling us?

                            It's impossible for this code:
                            $sql = 'SELECT user_name '
                            . ' FROM table_password '
                            . ' WHERE 1 AND user_password '
                            . ' LIKE '$code' LIMIT 0, 30';

                            to generate this error:
                            Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_password = 'abcdef' LIMIT 0, 30' at line 1

                            Show us the actual code you're using.

                            $thisshouldwork="
                            SELECT user_name
                            FROM table_password
                            WHERE user_password='$code'
                            ";

                              Thanks to the enthusiasm I received from all friends in this forum.

                              I have just resolved the problem before coming to this forum again.

                              The column name I used in MySQL database table was written in UTF8 Simplified Chinese characters. After I turned it into pure English, it runs fine.

                              Sorry I didn't tell all that I use non-English characters...because if I type in Chinese in this forum, I guess not many people know what I am talking about.

                              I have however encouter a new problem...let me start a new thread. At again, thanks to all.

                                Sorry I have to correct something.

                                I used phpMyAdmin to create the database and the table, while I was naming the columns, the encoding was GB2312 because there was no unicode options in phpMyAdmin.

                                The site was written in UTF-8 Simplified Chinese, so while I was querying the database, the query condition consisted of UTF-8 Simplified Chinese characters.

                                Thanks to all. And I hope that some other Asian newbies can learn from my experience.

                                  Write a Reply...