PHP/MySQL array

Troubles getting a dynamic array to work

[kieftrav]

Hey, there. I'm new to PHP/MySQL and I seem to be having a very simple problem (but isn't that simple for me...) I have a script that's supposed to return a dynamic array, but the array comes out funny. Here's the script

$special_query = mysql_query("SELECT columnb FROM tablea WHERE columna=some_value");
$special_query2 = mysql_fetch_array($special_query);

print_r($special_query2);

--And this is the output

Array ( [0] => row1columnb [columnb] => row1columnb )

Am I not understanding the basics or something? The [0] turned out right but why is the next one labeled [columnb]? I also have many more values in columna that equal some_value... Please help!

[barns101]

That's perfectly normal. Most people write the code like this:

$special_query = mysql_query("SELECT columnb FROM tablea WHERE columna=some_value");
$row = mysql_fetch_array($special_query);

That gives you an array $row[] which is accessed by $row[field_name_1], $row[field_name_2] etc...

[kieftrav]

I appreciate your help Barns101, and I understand what you're saying... It's just that my array is turning out like that... It happens so that $row[field_name_one] then it says $columnB[field_name_one]. The database doesn't have double value for [field_name_one]. It shouldn't return the same value twice, then, right? I mean, it's a foreign key in there, it CAN'T be the same value... But it is... Could it be something with the server?

[barns101]

As far as I am aware, the keys are always returned twice in this type of array. Mine certainly work the same way as yours.

[Psychopsia]

Yes, each row return twice, one for numeric representation and the second for the real stored row.

Like this:

Array
(
 [ 0] => 'value'
 ['field_name1'] => 'value',
 [ 1] => 'value2',
 ['field_name2'] => 'value2',
 [ 2] => 'value3',
 ['field_name3'] => 'value3',
 ...
)

[edited by: Psychopsia at 6:32 pm (utc) on Sep. 16, 2006]

[kieftrav]

Thanks Barns101 and Psychopsia, but why won't the rest of the field names display?

[eelixduppy]

>>>but why won't the rest of the field names display?

Because of your query. If you want all columns, then change your query to something like this:

$special_query = mysql_query("[b]SELECT * FROM tablea WHERE columna=some_value[/b]");
$special_query2 = mysql_fetch_array($special_query);
echo '<pre>';
print_r($special_query2); 
echo '</pre>';

Good luck!

[kieftrav]

Thanks, eelixduppy for the help, but it didn't change the outcome. Does it make a difference that my WHERE clause is column_a='$_SESSION[Username]'?

[Psychopsia]

> Does it make a difference that my WHERE clause is column_a='$_SESSION[Username]'?

No, is the same for this query, but you should use mysql_escape_string() function:

$sql = "... column_a = '" . mysql_escape_string($_SESSION['Username']) . "'";

[kieftrav]

Thanks, Psychopsia, but still no results... I just get the same query results... Any other ideas?

[eelixduppy]

kieftrav, are you trying to get all of the rows or all of the columns? My previous post is for all columns, but it will only return ONE row. If you want ALL ROWS AND ALL COLUMNS then you should do something like this:

[pre]
$special_query = mysql_query("SELECT * FROM tablea WHERE columna=some_value");
while($row = mysql_fetch_array($special_query)) {
  echo '<pre>';
  print_r($row);
  echo '</pre>';
}
[/pre]

There is more information regarding this subject in our library [webmasterworld.com]. Here's one such thread: [webmasterworld.com...]

I hope this is what you are looking for. By the way, Welcome to WebmasterWorld! (I forgot to welcome you).

Best of luck! ;)

[kieftrav]

eelixduppy, I would have to say that I'm looking for all rows that match the WHERE clause to be returned as an array. In other words, I want to select from tablea all values in columna_fields that match with columnb_fields=$_SESSION['Username']. Another example

TABLEA

COLUMNA ¦ COLUMNB
a ¦ 1
a ¦ 2
a ¦ 3
a ¦ 4
b ¦ 1
b ¦ 2
c ¦ 3

Let's say I wanted to have an array of all values in COLUMNB where COLUMNA=a. How would I form that query in PHP?

I'm sorry for all the confusion!

[eelixduppy]

Try this:

[pre]
$colb = [url=http://www.php.net/manual/en/function.array.php]array[/url]();
$special_query = mysql_query("SELECT columnb FROM tablea WHERE columna=some_value"); 
while($row = mysql_fetch_array($special_query)) { 
  [url=http://www.php.net/manual/en/function.array-push.php]array_push[/url]($colb,$row['columnb']);
}
echo '<pre>';
print_r($colb);
echo '</pre>';
[/pre]

Hope this is it! Good luck :)

[kieftrav]

Hey, eelixduppy, I still get the same results... Here is my exact code...

$colb = array();
$special_query = mysql_query("SELECT interests FROM student_interest WHERE email_address='" . $_SESSION['Username'] . "'");
while($row = mysql_fetch_array($special_query))
{
array_push($colb,$row['interests']);
}
echo '<pre>';
print_r($colb);
echo '</pre>';

[Psychopsia]

$sql = 'SELECT interests FROM student_interest WHERE email_address = '" . $_SESSION['Username'] . "'"';
$result = mysql_query($sql);

$data = array();
while ($row = mysql_fetch_array($result))
{
$data[] = $row;
}

[kieftrav]

It worked! Thanks Psychopsia! I also want to thank eelixduppy for continually providing feedback! I now have an array that works right! Thank you VERY much!

Travis

[eelixduppy]

It's interesting why array_push isn't working, because it should. Psychopsia's solution does almost the exact same thing. Anyway, glad it works! ;)