Take information from one array and put it into another with php

[garyjohnson]

Okay what I am trying to do might be hard to understand, but I will give it my best shot. I have created a script which selects data randomly from a table called albumname in a databse called characters. This array is then used again in a mysql_query to select the table from a different database named photos. It does this correctly. The problem occurs when I try to put the results from the mysql_query into a new array. It will only put the first result into the array and forget the rest. Here is the script,

<?php 
    $con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("characters", $con);

$query="SELECT album FROM albumname ORDER BY RAND() LIMIT 9";

$result= mysql_query($query) or die(mysql_error());
$i=0;
while($row = mysql_fetch_array($result)){
    $images[$i] = $row[0];
    $i++;   
}

?>
<?php 
    $con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }



mysql_select_db("photos", $con);
//The problem occurs here.
    $q = 0;
    $query2="SELECT Location FROM $images[$q] WHERE Pic_id='1'";
    $data= mysql_query($query2) or die(mysql_error());
    while( $info = mysql_fetch_array( $data ) && $q < 9){
        $query2="SELECT Location FROM $images[$q] WHERE Pic_id='1'";
        $data= mysql_query($query2) or die(mysql_error());
            $info2[$q] = $info[0];  
    $q++;
    }   

mysql_close($con);


?>

[TonyG_cyprus]

RE 'php mysql query wont work properly', you're still trying to SELECT from an
array rather than a database table aren't you? I don't see anywhere prior to the
query where "$images[$q]" is defined

[simplypixie]

If I understand correctly what you are trying to do, why on earth do you have 2 separate databases for your albums and images rather than 2 tables in one database?? There are other errors in your code, firstly $images[$i] = $row[0]; which will just keep populating your images array with the first result from your query results as you only ever assing the value at position 0. Secondly you cannot use table names like FROM $images[$q] as that would try and select from a table called, for example, FROM tablename[0] and as you cannot name tables like that, it just won't work.

What I suggest is chaning your db structure to one database with all your required tables and then you have 2 options to get your albums and related photos. The first is to run a nested query and while loop:

<?php 
    $con = mysql_connect("localhost","root","");
  if (!$con)
    {
    die('Could not connect: ' . mysql_error());
    }

mysql_select_db("new_db_name", $con);

$query="SELECT id, album FROM albumname ORDER BY RAND() LIMIT 9";

$result= mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
    $query2="SELECT Location FROM photos WHERE id='".$row['id']."'";
    $result2 = mysql_query($query2) or die(mysql_error());
    echo $row['album'].'<br>';
    while ($row2 = mysql_fetch_array($result2)) {
      echo $row2['Location'].'<br>';
    }
}

mysql_close($con);

?>

The second way is to use a join but may confuse you even more at the moment so I will leave it to you to decide and look into further if you wish.

[diafol]

This work?

mysql_select_db("characters", $con);
$query="SELECT album FROM albumname ORDER BY RAND() LIMIT 9";

$result= mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
    $sql_array[$i] = "SELECT Location FROM {$row[0]} WHERE Pic_id=1"; 
}
$sql = implode(" UNION ALL ", $sql_array);


mysql_select_db("photos", $con);
//The problem occurs here.
$data= mysql_query($sql) or die(mysql_error());
while( $info = mysql_fetch_array( $data )){
   echo $info[0] . "<br />";  
}   

Your Pic_id =1 may fail if you've deleted it. How about

"SELECT Location FROM {$row[0]} LIMIT 1"

[diafol]

Can't edit at the mo - there was a mistake:

 $sql_array[$i] = "SELECT Location FROM {$row[0]} WHERE Pic_id=1";

should be:

 $sql_array[] = "SELECT Location FROM {$row[0]} WHERE Pic_id=1";

[garyjohnson]

Diafol I think you know exactly what I'm trying to do. I tried your code but unfortenutely it only got me as far as mine did. It only stores one value into the Info[0] Array, when there should be 9. I have tried doing this

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("characters", $con);
$query="SELECT album FROM albumname ORDER BY RAND() LIMIT 9";
$result= mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
     $sql_array[] = "SELECT Location FROM {$row[0]} LIMIT 1"; 
}


$sql = implode(" UNION ALL ", $sql_array);

mysql_select_db("photos", $con);
//The problem occurs here.
$i=0;
$data= mysql_query($sql) or die(mysql_error());
while( $info = mysql_fetch_array( $data )){
   $info2[$i] = $info[$i];
   $i++;  
}
echo $info2[0];
echo $info2[1];
?>

It still only stores the value into the $info2[0] array spot and the rest of the array is blank.

[LastMitch]

@garyjohnson

I tried your code but unfortenutely it only got me as far as mine did. It only stores one value into the Info[0] Array, when there should be 9. I have tried doing this

What are you trying to accomplished here? I don't understand logically what you are trying to do. Can you post your table?

May I ask you this question is it much easier just using something like this:

Instead of this:

$sql_array[] = "SELECT Location FROM {$row[0]} LIMIT 1"; 

Can't you used something simliar to this:

<?php
$sql = "SELECT key,value FROM albumname";
$result = mysql_query($sql);
while($row = mysql_fetch_row($result)) {
$sql_array[$info2[$i]] = $info[$i];
}
while(list($key,$value) = each($sql_array)) {
echo "$key : $value";
}
?> 

[garyjohnson]

I'm not pro at php as you can tell but i'm not sure if your solution will work. I'll try to explain it more. I have two databases, and there needs to be two. One database is called characters with one Table called albumname and looks like this

Databse: Characters
Table: albumame

Album     |    char1     |     char2    |
-----------------------------------------
alyson    |    xxxxx     |     xxxxx    |
austins   |    xxxxx     |     xxxxx    |
emma      |    xxxxx     |     xxxxx    |
bella     |    xxxxx     |     xxxxx    |
gary      |    xxxxx     |     xxxxx    |
kayla     |    xxxxx     |     xxxxx    |
katie     |    xxxxx     |     xxxxx    |
kids      |    xxxxx     |     xxxxx    |
ware      |    xxxxx     |     xxxxx    |

The char1, and char2 columns do not matter for this. But as you can see in the album column there are 9 names, these are 9 names of tables in a different database. So the first mysql_query takes those 9 names randomly and puts them into an array.

From there the array is used in another mysql_query which looks like this
"SELECT Location FROM {$row[0]} LIMIT 1"
It should put all 9 array results into another array. So lets say $row[0] = kayla. Then the mysql_query should look like this "SELECT Location FROM kayla LIMIT 1"
it should do this 9 times, every time it does it the mysql_query is saved into another array which is $sql_array[].

This is when we use a different databse called photos and that looks like this.

 Databse: Photos

    Tables |
    -------
    alyson |
    austins|
    emma   |
    bella  |
    gary   |
    katie  |
    kayla  |
    kids   |
    ware   |

Every table above looks like this,

    Databse: Photos
    Table: alyson

    Pic_id     |    Location     |     name     |
    -----------------------------------------
    1          |    small.jpg    |     xxxxx    |
    2          |    1234.jpg     |     xxxxx    |

The only thing needed from this table is the location.

Then that array is placed into a mysql_fetch_array()
where it pulls 9 Location data's, these 9 location are then saved into a final array called $info[]. But this doesnt happen, Only one value is saved into the $info[] array and thats $info[0]. There should be 9 pieces of data in the $info[] array. Not one.

Hopefully this help.

[garyjohnson]

As it turns out Diafol was right. Thanks man!

[LastMitch]

@garyjohnson

I'm not pro at php as you can tell but i'm not sure if your solution will work.

I'm not a pro either. But the past 6 threads that you created in the past day your php and mysql is not good. I be honest with you. You been on Daniweb for like 5 months now. I end up rewriting the whole query for you? Trying to explain how to write a query.

For example like these threads:

http://www.daniweb.com/web-development/php/threads/444235/put-database-results-into-html-drop-down-menuI

http://www.daniweb.com/web-development/php/threads/444287/php-mysql-query-wont-work-properly

If you expect me to help you in the future. Try not to be rude. I have seen Daniweb members say awful things to members that are helping them and in the end the members avoid those rude members threads because of being rude. If you rub people in the wrong way. Noone will help you. That's the fact. I won't help you either. That's something I learn.

[garyjohnson]

I am completely sorry if I have offeneded you in any way! You have been tremendous help for me and I completely appreciate everything you have helped me out with. I appologize if I offeneded you!

[diafol]

I think that keeping the same requests to single threads can help with regard to this issue. Some of us - the more thin-skinned - and I include myself here - can get a bit narked with 'almost duplicate' threads. :)