Page 1 of 1

Getting Rows from MySQL

Posted: Sat Nov 08, 2008 12:25 am
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>";
    }
?>
 

Re: Getting Rows from MySQL

Posted: Sat Nov 08, 2008 7:46 am
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

Re: Getting Rows from MySQL

Posted: Sat Nov 08, 2008 3:12 pm
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.";
}
...