Help with database information extraction

[Ryflex]

Hi all,

 

I'm trying to figure out how I can get my code to get me some information in a table from the database.

The information from the database are a few city names. In the table will be a lot of city names from a lot of different users. I want the code to only print the city's of the specified user ($member_id).

Can anyone give me hint (not immediately all the code want to try myself but I do need a nudge in the right direction).

Btw table in database has the columns: ID, member_ID, X, Y

Where ID is the unique CityID and the rest speaks for itself (X,Y is coördinates)

Thnx Ryflex

[flemingmike]

$memberid='Mike';

$result = mysql_query("SELECT * FROM //table goes here\\ WHERE member_ID = '$memberid'");
while($row = mysql_fetch_array($result))
{

$city=$row['ID']; 
$x=$row['X']; 

and so on here.....

  }

[Ryflex]

@flemingmike

 

Ok got the code working but it only outputs the last city in the table...

How will I be able to make to my code output al entries???

 

Thnx Ryflex

[yaMz]

Nudge

http://php.net/manual/en/control-structures.foreach.php

[Ryflex]

@yaMz

 

Hi, thnx for the nudge ;-)

I made this out of it but it only gives me the first row in the database twice.

 

$result                     = mysql_query("SELECT *
                                           FROM city
                                           WHERE member_ID = '$member_ID'");
$array = mysql_fetch_array($result);

foreach ($array as $value)
{
     echo $value . "<br />";
//echo "city     = $city <BR>
  //    cityname = $cityname <BR>
    //  X        = $x <BR>
      //Y        = $y";
}   unset($value);
      ?>

 

What did I do wrong

Thnx Ryflex

[Ryflex]

@yaMz

 

Hi, thnx for the nudge ;-)

I made this out of it but it only gives me the first row in the database twice.

 

$result                     = mysql_query("SELECT *
                                           FROM city
                                           WHERE member_ID = '$member_ID'");
$array = mysql_fetch_array($result);

foreach ($array as $value)
{
     echo $value . "<br />";
}   unset($value);
      ?>

 

What did I do wrong

Thnx Ryflex

 

This is better deleted a part with commentary slashes but still it seems stuck on the first row of the database table

Ryflex

[Ryflex]

Hi all,

Since I'm still failing to see the problem I'm putting about every scrap of info about this problem on here.

 

Database Table:

 

Code:

$query                      = ("SELECT *
                                FROM city
                                WHERE member_ID = '$member_ID'");
$result                     = mysql_query($query);
$array                      = mysql_fetch_array($result);
function print_all($array)
{
     foreach ($array as $value)
     {
          print("$value<br>");
     }
}
print_all($array);

Output:

1

1

1

1

Amsterdam

Amsterdam

0

0

0

0

 

Is there anyone out there who might have a clue what's wrong here....

I changed the member_ID of Amsterdam to 2 and then you get

2

2

1

1

Leiderdorp

Leiderdorp

0

0

0

0

 

About now I'm starting to think about hammers and my laptop.....

Anyone HELLLPPPPP

Ryflex

[Pikachu2000]

You don't need to bother trying to store the results in another array, then using a foreach loop to iterate through them if all you want to do is list the results out.

 

<?php
$query = "SELECT `Name` FROM `city` WHERE `member_ID` = $memberid";
$result = mysql_query($query) or die( mysql_error() );
while($row = mysql_fetch_assoc($result)) {
echo $row['Name'] . '<br>';
}
?>

[mikosiko]

 

Is there anyone out there who might have a clue what's wrong here....

 

About now I'm starting to think about hammers and my laptop.....

Anyone HELLLPPPPP

Ryflex

 

Picachu's answer gave you a different approach to solve your problem, but just to give you an exact answer to your original problem and question:

 

what is wrong with your code?  and why you are getting duplicated results?

 

What you are seeing is the result of use mysql_fetch_array() in your code... this is what mysql_fetch_array() does:

mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both

 

and this is the format

array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )

 

where $result_type can be MYSQL_BOTH (the default), MYSQL_NUM or MYSQL_ASSOC

 

therefore if you don't specify $result_type mysql_fetch_array() will always return the fetched row twice, once with numeric array indexes and once with associative array indexes

 

notice how Picachu changed the mysql_fetch_array() for the alternate form mysql_fetch_assoc()  the equivalent to mylsq_fetch_array($result, MYSQL_ASSOC)

 

hope this help you for the future

[Ryflex]

@Pikachu2000

Thnx very much for the solution it works perfectly

 

@mikosiko

I tried what you said and added the MYSQL_ASSOC in the mysql_fetch_array, but it only made sure I only got the result only once. I thought it was the meaning an array would be able to contain the information of multiple rows.

Anyway I do thank you also for giving me more understanding in the working of arrays.

 

Ryflex

Burn Rubber not Your Soul