Getting Rows from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
~ Zymus ~
Forum Newbie
Posts: 11
Joined: Wed Nov 05, 2008 8:36 pm

Getting Rows from MySQL

Post by ~ Zymus ~ »

Hello,
I have a problem with my PHP script. Im trying to test if a row has the same value as a user input.

I want to test and see if $Username is equal to a specific row in the $Usernames Rows.

Code: Select all

 
<?php
$Username = $_POST["Username"];
$Password = $_POST["Password"];
 
$Connected = mysql_connect("127.0.0.1:8889", "Zymus", "carpediemcarpenoctem");
 
if($Connected)
    {
        $SelectedDB = mysql_select_db("oraculum");
       
        if(!$SelectedDB)
            {
                echo mysql_error();
            }
       
        $Query = "SELECT Username, Password FROM Information";
        $Result = mysql_query($Query);
       
        if($Result)
            {
                $Information = mysql_fetch_array($Result);
               
                if($Information)
                    {
                        echo "Retrieved Result";
                        echo "<br>";
                    }
               
                else
                    {
                        echo "Failed";
                        echo "<br>";
                    }
               
                while($Information)
                    {
                        $Usernames = $Result['Username'];
                       
                        if($Usernames[$Index] == $Username)
                            {
                                echo "Successful";
                                echo "<br>";
 
                               break;
 
                            }
                       
                        else
                            {
                                echo "$Usernames[$Index]";
                            }
                    }
            }
       
        else
            {
                echo mysql_error();
            }
    }
 
else
    {
        echo mysql_error();
    }
?>
 
Result:
Retrieved Result
What am i doing wrong? I want it to test if the row value equals the $Username value, then say successful, and stop. It doesn't test if it's equal or not... Should i be using a for loop, or am i just doing something wrong?

EDIT: I fixed it. Here's the code i used:

Code: Select all

 
<?php
$Username = $_POST["Username"];
$Password = $_POST["Password"];
 
$Connected = mysql_connect("127.0.0.1:8889", "Zymus", "carpediemcarpenoctem");
 
if($Connected)
    {
        $SelectedDB = mysql_select_db("oraculum");
        
        if(!$SelectedDB)
            {
                echo mysql_error();
                echo "<br>";
            }
        
        $Query = "SELECT `Username`, `Password` FROM `Information`";
        $Result = mysql_query($Query);
        
        if($Result)
            {
                $Information = mysql_fetch_array($Result);
                
                /*if($Information)
                    {
                        echo "Retrieved Result";
                        echo "<br>";
                    }
                
                else
                    {
                        echo mysql_error();
                        echo "<br>";
                    }*/
                
                $NumberOfRows = mysql_num_rows($Result);
                
                for($Index = 0; $Index < $NumberOfRows; $Index++)
                    {
                        $DBUsername = mysql_result($Result, $Index, "Username");
                        $DBPassword = mysql_result($Result, $Index, "Password");
                        
                        if($DBUsername == $Username)
                            {
                                if($DBPassword == $Password)
                                    {
                                        echo "Successfully Logged In As: $Username";
                                        echo "<br>";
                                        echo "With Password: $Password";
                                        echo "<br>";
                                        
                                        break;
                                    }
                                
                                else
                                    {
                                        echo "Invalid Password";
                                        echo "<br>";
                                    }
                            }
                        
                        else
                            {
                                echo mysql_error();
                                echo "<br>";
                            }
                    }
            }
        
        else
            {
                echo mysql_error();
                echo "<br>";
            }
    }
 
else
    {
        echo mysql_error();
        echo "<br>";
    }
?>
 
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

Re: Getting Rows from MySQL

Post by Jaxolotl »

Firts of all BEWARE!!! your code is far away from being safe!!!! Make a search on this forum about SQL Injection
then
Why to restrieve all your records and then use PHP to compare strings with results ?

There are many SQL statemes to do it selecting only the records that match the comparison statement

This way you may query something like this

Code: Select all

 
SELECT `field_x`,`field_y` FROM `my_table` 
WHERE 
`field_x` = '".$my_sanitized_value."'
AND `field_y` = '".$my_also_sanitized_value."'
 
http://dev.mysql.com/doc/refman/4.1/en/query-speed.html
http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Getting Rows from MySQL

Post by califdon »

What am i doing wrong?
You have to first understand what you're trying to do. Then you have to understand the syntax that is used to do it.

So are you trying to compare what the user enters with a value stored in a table that has only ONE ROW? Probably not. Therefore, your SQL query string doesn't make any sense. You need to add the WHERE clause that limits the results to a row (or rows) where the contents of some particular column matches your user input.

Then, if there's even a remote possibility that more than one row could match, you have to fetch the rows in the results array, one at a time and do whatever you need to do. The sequence of operations must be:
  1. Connect to your database server
  2. Select your database
  3. Execute your query and return the results array
  4. Fetch the rows in a WHILE loop
  5. Process the rows
The following code will certainly not work directly with your database, but perhaps you will be able to see how this is done.

Code: Select all

...
$usr=mysql_real_escape_string($_POST['Username']);
$pwd=mysql_real_escape_string($_POST['Password']);
mysql_connect("localhost","root","mypassword") or die("Couldn't connect! " . mysql_error());
mysql_select_db("oraculum") or die("Unable to select database! " . mysql_error();
$sql="SELECT Username, Password FROM Information WHERE Username='$usr'";
$result=mysql_query($sql) or die("Select Query failed! " . mysql_error());
if($result) {
    while($row=mysql_fetch_assoc($result)) {
        if($pwd==$row['Password']) {
            echo "Password authenticated.";
        } else {
            echo "Password not valid.";
        }
    }
} else {
    echo "Username not found.";
}
...
Post Reply