Retrieving multipĺe records from MySQL

[richiman]

Hello,

 

This is driving me crazy, as I cannot get it working.

 

I have a database that has a table called 'categories'. I need to retrieve all categories with category_id != '1,2,3'. For this, I can this query:

 

$q = "SELECT * FROM categories WHERE category_id NOT IN (1,2,3

)";

 

The query executes fine. I need this categories retrieved, to be passed to a Smarty Template Engine powered page. I have:

$q = "SELECT * FROM categories WHERE category_id NOT IN (1,2,3)";
$res = mysql_query($q);
while($row=mysql_fetch_array($res)) {
Assign('categories',$row);
}

 

But when retrieving the array in the Smarty page, with a loop, I only see the last item of the array (the last category).

 

If I go back to the while loop, and add a

print_r($row);

, I see two arrays (I have two categories).

 

But the question is: How can I work with those two categories, as I need to use them individually outside the while loop?

 

Any help is appreciated.

 

Best Regards,

Richi

[The Little Guy]

I haven't used smarty, but I would assume it would look like this:

 

$q = "SELECT * FROM categories WHERE category_id NOT IN (1,2,3)";
$res = mysql_query($q);
while($row=mysql_fetch_array($res)) {
Assign('categories',$row["Column_Name_Here"]);
}

[richiman]

Hello,

 

Thanks for the help, yet that's not what I need , it's kinda hard to explain, though.

 

The query, fetches two rows, but when I pass var to smarty, it only has the last one. I need to work with each of them different, if I could add lets say row1 as $cat1arr, row2 as $cat2arr, would be great!

 

I hope I was clear and somebody can help me. I appreciate all the help!

 

Regards,

Richi

[Jessica]

If you look at the docs you can see that you are assigning $row to a variable which is NOT an array.

http://www.smarty.net/docs/en/language.syntax.variables.tpl

 

You need to have categories be an array, I'd suggest reviewing that page.

[richiman]

Hello,

 

Categories is actually an array that contains multiple elements. Let's forget about Smarty, and simply try to do it in PHP. I have a parent category and a sub category. Each sub category has mutiple items.

 

I want to display all sub categories and display all items the sub category has.

 

Subcat

->Item

->Item

->Item

Subcat

->Item

->Item

->Item

Subcat

->Item

->Item

->Item

 

When I know the subcategory id, it will now look into another table to find all items that have that belong to that sub category. For example, I need to fetch items from sub cat 1,2,3.

 

I can think of a for() loop, counting all the subcategories we are going to fetch if they where in an array with count().

$catsarr = array(1,2,3); //All the cats must be retrieved from the db. This is what is causing me trouble to achieve.

 

for($i=1;$i<=count($catsarr);$i++) {
$q= "SELECT * FROM items WHERE subcategory = {$i}";
$res=mysql_query($q);
while($row=mysql_fetch_array($res)) {
print "Sub category".$i."<br />";
//HERE I NEED A LOOP TO SHOW ME ALL THE VIDEOS THAT ARE FROM THAT CATEGORY. DON'T KNOW HOW TO.
}
}

 

It's kinda confusing for me to explain. If someone does really want to help me and has the time, we can have a chat. I'd definitely appreciate it. Again, any help is greatly appreciated.

 

Regards,

Richi

[Jessica]

Ah. The way I would do it is to join the videos to both categories and subcategories ordered BY category and subcategory, so you have an array of all the videos with what groups they belong in. Create a variable for cat = '' and subcat = ''. Then loop through that array of videos. If the current video is in a different cat/subcat than the value in the variables, echo the HTML for the cat's name. Otherwise echo the video name. Always end by storing the current cat/subcat in that var.

 

<?php
$cat = '';
$subcat = '';
$sql = "SELECT ... " // Do your join here based on category and subcat relationship
$res=mysql_query($q);
while($row=mysql_fetch_array($res)) {
   if($res['cat'] != $cat){
     //It's the start of a new category
   }
   if($res['subcat'] != $subcat){
     //It's the start of a new sub-category
   }
   echo $res['video'];
   $cat = $res['cat'];
   $subcat = $res['subcat'];

}
?>

[richiman]

Hello,

 

I'm trying to create the SQL query, but I am having trouble doing it. So far, I have:

 

$q="SELECT cb_video_categories.category_name, cb_video "."FROM cb_video_categories,cb_video "."WHERE cb_video.category = cb_video_categories.category_id";

 

But the problem, is that cb_video.category records, have # symbols. I mean, category is wrapped like this: #5#. How can I make it override those symbols?

 

My query will look for the category name and will fetch all the rows of cb_video (item).

 

Regards,

Richi

[richiman]

I know quoting in the above query is wrong, just noticed, but can't edit anymore.

[Jessica]

Hello,

 

I'm trying to create the SQL query, but I am having trouble doing it. So far, I have:

 

$q="SELECT cb_video_categories.category_name, cb_video "."FROM cb_video_categories,cb_video "."WHERE cb_video.category = cb_video_categories.category_id";

 

But the problem, is that cb_video.category records, have # symbols. I mean, category is wrapped like this: #5#. How can I make it override those symbols?

 

My query will look for the category name and will fetch all the rows of cb_video (item).

 

Regards,

Richi

 

Well, don't do that...why do they have hash marks around them?

[Andy-H]

$q="SELECT cb_video_categories.category_name,  cb_video_categories.cb_video FROM cb_video_categories, cb_video WHERE REPLACE(cb_video.category, '#', '') = cb_video_categories.category_id";

 

 

 

Should probably just run:

 

 

UPDATE cb_video SET category = REPLACE(category, '#', '');

 

 

in phpmyadmin to replace all #'s and change the type to integer as programming languages handle integers much faster.

[richiman]

Hello,

 

They have #, because there are cases, where multiple categories exist there: #2# #24# etc. It's a premade script called Clip-Bucket, so making them without # will affect other functions as well.

 

I'll try what you just told me. Will keep you updated.

 

Thanks for the help.

 

Regards,

Richi

[Jessica]

If the child needs to be in several parent categories, you can separate it out into another relational table.