SQL Select Query not returning the correct data!

[RabbitFactoryUK]

Hi All

 

Ok I would just like to say thank you to anyone that can help.

 

I am getting a weird thing that I have never come accross before, the chances are that this is probaly a silly mistake but I have tried everything to get this working.

 

What I have is a simple Login System and what I want to do is get a array of the users from the database so that I can 'echo' these out within the page.

 

I have a function that gets this information and returns the array:

function get_users()
{
    $query = "SELECT id,usr,dob FROM ".tbl_users; 
    	 
    $result = mysql_query($query) or die(mysql_error());
    
    $row = mysql_fetch_array($result) or die(mysql_error());
    return $row;   
}

 

The functions page is required at the top of the page that I want the data to appear, I am then putting the data into a var and running a foreach to echo out each field:

 

$matches = get_users();
     foreach ($matches as $user)
     {
        echo $user['usr'];
     }

 

seems all well and good to me however I have only one record in the database and this is a temporay record used for testing purposes:

 

id: 7

usr: Oxygen2010

dob: 1985-05-07

 

but what this script is giving me is the following:

 

77OO11

 

now i'm assuming that one of the 7's is the id and the rest looks like the first letter/number from each field but duplicated?

 

HELP

 

Thanks

 

[Pikachu2000]

You're using mysql_fetch_array(), which returns an array containing two sets of the same data. One array is associative, the other is enumerated. Since you're using a foreach() loop and don't need the associative array, you can use mysql_fetch_row() instead. As for the truncated values, what is the data type of each of those fields in the DB table?

[RabbitFactoryUK]

Thanks for your reply Pikachu2000,

 

I have changed fetch_array to fetch_row and this seems to have solved the problem of the duplication however the issue of truncation is still present, my data types are as follows :

 

id - int(11)

usr - varchar(32)

dob - date

 

I can't see this being the problem though as I am successfully use the data on the main site, i.e the actual user login im confused!

 

[RabbitFactoryUK]

after abit more trial and error I have noticed that the data is being passed correctly from the function to the $matches var (used the print_r function).

 

Which leads me to belive that there is a problem with the for each?

[Pikachu2000]

Post the output this code gives you . . .

 

$matches = get_users();
     foreach ($matches as $user)
     {
        print_r($user);
     }

[RabbitFactoryUK]

7Oxygen20101985-05-07

 

That appears to give me the correct information but when I try to echo out the fields individually it comes back with the 1 char

 

[wildteen88]

Because $matches contains an array of fields, not an array of results!

[Pikachu2000]

For the foreach() loop to work correctly, you need  the function to return a multidimensional array. Make the following change, and I believe you should be good to go.

 

$row[] = mysql_fetch_assoc($result) or die(mysql_error()); // Added square brackets, and changed to mysql_fetch_assoc . . .

[RabbitFactoryUK]

thanks that seems to have worked but... :

 

function:

function get_users()
{
    $query = "SELECT id,usr,dob FROM ".tbl_users; 
    	 
    $result = mysql_query($query) or die(mysql_error());
    
    $row[] = mysql_fetch_assoc($result) or die(mysql_error());
    return $row;   
}

 

$matches = get_users();
     foreach ($matches as $user)
     {
       echo $user['id']."<br/>";
       echo $user['usr']."<br/>";
       echo $usr['dob'];
     }

 

 

with the result of:

 

7

Oxygen2010

O

 

as you can see the date is not coming out correctly? could this be something to do with dob being a date datatype?

[Pikachu2000]

OK, now what does this output with the one line I added to the foreach()?

 

$matches = get_users();
     foreach ($matches as $user)
     {
       echo '<pre>'; print_r($matches); echo '</pre>';
       echo $user['id']."<br/>";
       echo $user['usr']."<br/>";
       echo $usr['dob'];
     }

[RabbitFactoryUK]

it's showing correct? this again is probaly really simple, I really shouldnt be doing things like this when Im half asleep lol

 

The output is:

 

Array

(

    [0] => Array

        (

            [id] => 7

            [usr] => Oxygen2010

            [dob] => 1985-05-07

        )

 

)

 

7

Oxygen2010

O

[Pikachu2000]

I see it now. Look at this line closely, compared to the others: echo $usr['dob'];

[RabbitFactoryUK]

omg! I told you I should not be doing this half asleep!!!

 

Thank you very much I really appreciate the help!

 

and I'm sure that I will be seeing you around these forums again as I WILL be sticking around! thanks

[Pikachu2000]

And I probably shouldn't be helping people half drunk . . .

 

 

 

 

 

 

 

 

 

 

 

 

 

[just kidding]