Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Im trying to generate an array but not sure how to go about it.

I'm currently getting my data like so:

$query = mysql_query("SELECT * FROM users WHERE userEmail LIKE '[email protected]'");
$row = mysql_fetch_array($query);

$query1 = mysql_query("SELECT * FROM categories");
while($row1 = mysql_fetch_array($query1)){

    $query2 = mysql_query("SELECT * FROM usersettings WHERE userId = ".$row['userId']." AND usersettingCategory".$row1['categoryId']." LIKE 'y'");
    $isyes = mysql_num_rows($query2);

    if($isyes > 0){

        $cat1 = mysql_query("SELECT * FROM shops WHERE shopstateId = 1 AND (categoryId1 = ".$row1['categoryId']." OR categoryId2 = ".$row1['categoryId']." OR categoryId3 = ".$row1['categoryId'].")");
        $cat1match = mysql_num_rows($cat1);

        if($cat1match > 0){
            while($cat1shop = mysql_fetch_array($cat1)){
                $cat1msg = mysql_query("SELECT * FROM messages WHERE shopId = ".$cat1shop['shopId']." and messagestateId = 1");
                while($cat1msgrow = mysql_fetch_array($cat1msg)){

                    echo $cat1msgrow['messageContent']." - ".$cat1msgrow['messageCode'];
                    $cat1img = mysql_query("SELECT shopimagePath FROM shopimages WHERE shopimageId = ".$cat1shop['shopimageId']);
                    $imgpath = mysql_fetch_array($cat1img);
                    echo " - ".$imgpath['shopimagePath']."<br/>";
                }
            }
        }
     }
}

But this can cause duplicates when a user has all 3 of a shops categories picked in their preferences. I am trying to find a way to just pull the message ID out instead of the whole thing and put it into an array giving me, for example:

1,3,5,7,1,3,5,2,4,7,8

Then I can just run a separate query to say get me all messages where the ID is in the array, but i am unsure of the most constructive way to build such an array and examples of array from a while loop I have seen do not seem to be what I am looking for.

Is there anyone out there that can push me in the right direction?

share|improve this question
 
why dont use! inner join, and can you show up the structures of tables.. –  Rafee May 1 '12 at 5:44
add comment

2 Answers

up vote 0 down vote

Can't help with this code. But if you want an array from a query without duplicate result, you can use " select DISTINCT (id) " in your query or for more simple solution :

$id_arr = array();
$sql = mysql_query("select id from id_table"); 
while ($id_result = mysql_fetch_array($sql) {
  $id  = $id_result['id'];
  if (!in_array($id, $id_arr)) {
    $id_arr[] = $id;
  }
}
share|improve this answer
add comment
up vote 0 down vote accepted

I have found a much easier way to create the required result. I think at 6am after a hard night coding my brain was fried and I was making things a lot more complicated than I needed to. A simple solution to my issue is as follows:

$query = mysql_query("SELECT * FROM users WHERE userEmail LIKE '[email protected]'");
$row = mysql_fetch_array($query);

$categories = "(";

$query1 = mysql_query("SELECT * FROM categories");
while($row1 = mysql_fetch_array($query1)){

    $query2 = mysql_query("SELECT usersettingCategory".$row1['categoryId']." FROM usersettings WHERE userId = ".$row['userId']);
    $row2 = mysql_fetch_array($query2);

    if($row2['usersettingCategory'.$row1['categoryId']] == y){

        $categories .= $row1['categoryId'].",";
    }

}

$categories = substr_replace($categories ,")",-1);

echo $categories."<br />";

$query3 = mysql_query("SELECT * FROM shops,messages WHERE shops.shopId = messages.shopId AND messages.messagestateId = 1 AND (shops.categoryId1 IN $categories OR shops.categoryId2 IN $categories OR shops.categoryId3 IN $categories)");

while($row3 = mysql_fetch_array($query3)){

    $query4 = mysql_query("SELECT shopimagePath FROM shopimages WHERE shopimageId = ".$row3['shopimageId']);
    $row4 = mysql_fetch_array($query4);

    echo $row3['messageContent']." - ".$row3['messageCode']." - ".$row4['shopimagePath']."<br />";
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.