quick help

[shenagsandnags]

i need some quick help, this project has been way over due and ive wasted huge amounts of time researching on my own and bothering others trying to figure this out and i just cant leave it be its like a damn drug. im desperate folks!

 

 

my 2 tables,

 

movies:    id - title - category - url

 

categories: id - category (FK)

 

what im trying to do is have every category list as a table and then list the movies owned by that category in them, example:

 

Category 1

---------------

title

title

 

Category 2

--------------

title

title

 

Category 3

--------------

title

title

 

 

the code im using is

 

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

mysql_select_db("mydb", $con);
$result = mysql_query("SELECT a.ID, 
       a.Title,
       b.Category,
       a.URL
  FROM Movies AS a 
     JOIN Categories b ON a.Category = b.ID
  ORDER BY b.Category");
echo "<table border='1'>
<tr>
<th>Title</th>
<th>Category</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td width='100'><a href='".$row['URL']."'>" . $row['Title'] . "</a></td>";
echo "<td width='150'>" . $row['Category'] . "</td>";
}
echo "</table>";

$result2 = mysql_query("SELECT ID,Category FROM Categories");

while($row2 = mysql_fetch_array($result2))
{
echo "<tr>";
echo "<td>" . $row2['Title'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>

 

but obviously this code gives me a single table that lists all the titles with category next to them and then under the table it lists my categorys from left to right and its looks like

 

Title    Category

---------------------

title1    Drama

title2    Comedy

title3    Horror

title4    Thriller

--------------------

DramaComedyHorrorThriller

 

 

 

what exactly do i need to change to display the results i want ?  btw dont hammer me on using 2 while loops, some tell me i need 2 and others say 2 is pointless. i myself have no clue!

 

 

 

 

[robert_gsfame]

i don't really get, but let see if my explanation is what you need. 

 

Based on what you've explained to us, you wish to have this

 

Category 1

---------------

title

title

 

Category 2

---------------

title

title

 

so why don't you check the what category exists (use DISTINCT in your mysql query) in table categories, loop it and then check what movies match the category and loop it either

 

so in this case still u have to use 2 times looping ..but for sure there are so many ways to get the same results. 

[shenagsandnags]

so why don't you check the what category exists (use DISTINCT in your mysql query) in table categories, loop it and then check what movies match the category and loop it either

 

im sorry im not exactly sure what you ment by that, i kind of do but i dont.  please dont assume that my echos and queries are right because the echos and queries you see in the code are just me trying to guess and hope they are correct. im confident that the first query is correct but anything else after that is more then likely going to be wrong for what im doing.

 

query #1 and the syntax to the while loops were wrote for me by someone else and i was told to type in the echos myself and then i was just left in the wind. they assumed i knew what i was doing i guess...  so i was kind of hoping that someone would tell me the correct queries and echos and send me on my marry way. IF its that simple because everyone has told me that it was but yet i dont know enough php yet.

 

i will take whichever way i can to get it to work or just even the simplest, if i need 2 loops then fine by me if i only need 1 then even greater. i dont really care just as long as it displays the way i need.

 

 

 

 

[zero_ZX]

I think your best shot is to split this into two pages, like you do in forums. You show the categories, then you show the topics.

So you have your categories.php or index.php or w/e where you use a mysql while loop and print the categories with a link to movies.php?cat=1 or w/e id it should be.

 

At the movies.php you then do the same, except in this while loop you will have have a where clause depending on the cat variable. Remember to use real escape string so no1 makes some nasty attacks.

 

If you need further help, please PM me, and i can code you two example pages to show you what i mean.

[robert_gsfame]

<?php

$con = mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("mydb", $con);

=================================================

(the way you join two tables seems to be wrong....as category and id cannot be connected)

movies:    id - title - category - url

categories: id - category (FK)

 

 

$result =mysql_query("SELECT DISTINCT category FROM categories");

$result_num=mysql_num_rows($result);

 

if(!empty($result))

{

  echo "<table>";

  while($result_array=mysql_fetch_array($result))

    {

      $category=$result_array['category'];

      echo "<tr><td style='border-bottom:dashed 1px #000000'>$category</td></tr>";

 

      $check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE category='%s'",

      mysql_real_escape_string($category)));

      $check_table2_num=mysql_num_rows($check_table2);

      if(!empty($check_table2_num))

      {

        while($check_table2_array=mysql_fetch_array($check_table2))

        {

          $title=$check_table2_array['title'];

          echo "<tr><td>$title</td></tr>";   

          }

      }

    }

  echo "</table>";

}

 

 

well this code is not yet tested, but i think it should work...hope that helps!

[shenagsandnags]

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mydb", $con);
=================================================
(the way you join two tables seems to be wrong....as category and id cannot be connected)
movies:    id - title - category - url
categories: id - category (FK)


$result =mysql_query("SELECT DISTINCT category FROM categories");
$result_num=mysql_num_rows($result);

if(!empty($result))
{
   echo "<table>";
   while($result_array=mysql_fetch_array($result))
    {
      $category=$result_array['category'];
      echo "<tr><td style='border-bottom:dashed 1px #000000'>$category</td></tr>";

      $check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE category='%s'",
      mysql_real_escape_string($category)));
      $check_table2_num=mysql_num_rows($check_table2);
      if(!empty($check_table2_num))
       {
         while($check_table2_array=mysql_fetch_array($check_table2))
         {
           $title=$check_table2_array['title'];
           echo "<tr><td>$title</td></tr>";     
          }
       }
    }
   echo "</table>";
}

 

 

 

 

dude, that is soooooooooo close!  the above code gives me

 

Drama

-------------

 

Comedy

-------------

 

Horror

-------------

 

Thriller

-------------

 

 

the only thing missing is it is not listing the movie titles under each category!  could it be a simple change in the query ?

sooo close!

 

[robert_gsfame]

i think i got this....

 

please specify link so that two tables can be connected

 

if i read what you've mentioned before, you might have to do this. try changing this part

$check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE category='%s'",

 

with this

$check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE ID='%s'",

 

and let see if it works

 

[shenagsandnags]

nope, no difference.

 

btw im not sure if this will help or not but heres an exact example of both tables,

 

Movies          (FK)

ID - Titles - Category - URL

2    Avatar        1          www.blahkjhkfdjk.com

 

Categories

ID  -  Category

1      Drama

 

just want to make sure i didnt leave anything out

[robert_gsfame]

After you changed this part

 

$check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE Category='%s'",

 

with this

 

$check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE ID='%s'",

 

and change this part either

 

$title=$check_table2_array['title'];

 

with this

 

$title=$check_table2_array['Titles'];

 

 

It will work...!!!! 100%

[shenagsandnags]

believe it or not still no difference, here is the exact code just to make sure that i didnt mess anything up myself.

 

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

mysql_select_db("mydb", $con);

$result =mysql_query("SELECT DISTINCT category FROM categories");
$result_num=mysql_num_rows($result);

if(!empty($result))
{
   echo "<table>";
   while($result_array=mysql_fetch_array($result))
    {
      $category=$result_array['category'];
      echo "<tr><td style='border-bottom:dashed 1px #000000'>$category</td></tr>";

      $check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE ID='%s'",
      mysql_real_escape_string($category)));
      $check_table2_num=mysql_num_rows($check_table2);
      if(!empty($check_table2_num))
       {
         while($check_table2_array=mysql_fetch_array($check_table2))
         {
           $title=$check_table2_array['Titles'];
           echo "<tr><td>$title</td></tr>";     
          }
       }
    }
   echo "</table>";
}

mysql_close($con);
?>

 

 

so close so close i can taste it!

 

[robert_gsfame]

<?php

$con = mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("mydb", $con);

 

$result =mysql_query("SELECT DISTINCT Category FROM categories");

$result_num=mysql_num_rows($result);

 

if(!empty($result))

{

  echo "<table>";

  while($result_array=mysql_fetch_array($result))

    {

      $category=$result_array['Category'];

      echo "<tr><td style='border-bottom:dashed 1px #000000'>$category</td></tr>";

 

      $check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE ID='%s'",

      mysql_real_escape_string($category)));

      $check_table2_num=mysql_num_rows($check_table2);

      if(!empty($check_table2_num))

      {

        while($check_table2_array=mysql_fetch_array($check_table2))

        {

          $title=$check_table2_array['Titles'];

          echo "<tr><td>$title</td></tr>";   

          }

      }

    }

  echo "</table>";

}

 

mysql_close($con);

?>

 

okay try that code...above, i just make a small change

category with Category

title with Titles

 

[shenagsandnags]

still no difference, lol your such a tease

[robert_gsfame]

<?php

$con = mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("mydb", $con);

 

$result =mysql_query("SELECT DISTINCT Category FROM Categories");

$result_num=mysql_num_rows($result);

 

if(!empty($result_num))

{

  echo "<table>";

  while($result_array=mysql_fetch_array($result))

    {

      $category=$result_array['Category'];

      echo "<tr><td style='border-bottom:dashed 1px #000000'>$category</td></tr>";

 

      $check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE ID='%s'",

      mysql_real_escape_string($category)));

      $check_table2_num=mysql_num_rows($check_table2);

      if(!empty($check_table2_num))

      {

        while($check_table2_array=mysql_fetch_array($check_table2))

        {

          $title=$check_table2_array['Titles'];

          echo "<tr><td>$title</td></tr>";   

          }

      }

    }

  echo "</table>";

}

 

mysql_close($con);

?>

 

just copy and paste above code...let see if it works

[robert_gsfame]

haha....actually i missed this part...so just change

 

if(!empty($result))

{

  echo "<table>";

  while($result_array=mysql_fetch_array($result))

 

with this

 

if(!empty($result_num))

{

  echo "<table>";

  while($result_array=mysql_fetch_array($result))

 

1000% WORK!!!

[shenagsandnags]

lol, still no difference. double check that i made correct changes.

 

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

mysql_select_db("mydb", $con);

$result =mysql_query("SELECT DISTINCT category FROM categories");
$result_num=mysql_num_rows($result);

if(!empty($result_numm))
{
   echo "<table>";
   while($result_array=mysql_fetch_array($result))
    {
      $category=$result_array['category'];
      echo "<tr><td style='border-bottom:dashed 1px #000000'>$category</td></tr>";

      $check_table2=mysql_query(sprintf("SELECT * FROM movies WHERE ID='%s'",
      mysql_real_escape_string($category)));
      $check_table2_num=mysql_num_rows($check_table2);
      if(!empty($check_table2_num))
       {
         while($check_table2_array=mysql_fetch_array($check_table2))
         {
           $title=$check_table2_array['Title'];
           echo "<tr><td>$title</td></tr>";     
          }
       }
    }
   echo "</table>";
}

mysql_close($con);
?>

 

 

[robert_gsfame]

if it's not working not because what i said...it's because you wrongly type this part

 

please change this part

if(!empty($result_numm))

 

with this (Only single "m")

if(!empty($result_num))

1000000 WORKS!!

[shenagsandnags]

lol STILL no difference.  if its working for you then what else could it be on my end ? or have you even tested it yourself ?

[PFMaBiSmAd]

To produce your desired output using your original query, you would simply 'remember' the current category and output a new category heading every time the category changes -

 

<?php
// execute query here

$last_category = ''; // initialize to a value that will never exist in the data
while($row = mysql_fetch_array($result)){
    // test if the category changed
    if($last_category != $row['Category']){

        // the category changed (or is the first one), output the $row['Category'] heading here...

        $last_category = $row['Category']; // 'remember' the new category value
    }

    // output the $row['Title'] data here ...

}
?>

 

In general, your query (one) should retrieve the data you want, in the order that you want it, and your presentation code should simply iterate over the data and display it the way you want.

[shenagsandnags]

 

like this ?

 

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

mysql_select_db("mydb", $con);
$result = mysql_query("SELECT a.ID, 
       a.Title,
       b.Category,
       a.URL
  FROM Movies AS a 
     JOIN Categories b ON a.Category = b.ID
  ORDER BY b.Category");
$last_category = ''; // initialize to a value that will never exist in the data
while($row = mysql_fetch_array($result)){
    // test if the category changed
    if($last_category != $row['Category']){

echo "<td width='150'>" . $row['Category'] . "</td>";

        $last_category = $row['Category']; // 'remember' the new category value
    }

echo "<td width='100'><a href='".$row['URL']."'>" . $row['Title'] . "</a></td>";

}

mysql_close($con);
?>

 

 

[PFMaBiSmAd]

You might want to make your use of <td></td> elements different as what you posted would just keep adding table data elements to a single row in your table.

 

Either eliminate all the table elements or put the Category in <tr><th> ... </th></tr> elements and put the Title <tr><td> ... </td></tr> elements.

[shenagsandnags]

ok so when i test the code i displays like this,

 

DramaMovie1Movie2ComedyMovie3HorrorMovie4Movie5

 

so technically speaking this is almost exactly what i wanted but obviously there is a problem with the "way" its displaying it which i assume thats why you were telling me about the elements.  im pretty sure i understood you correctly so i tried what you suggested and even took out the elements completely but the displays are still not changing, maybe there is something i am forgetting.

 

Even this does not change anything.

echo "<td><th>" . $row['Category'] . "</td></th>";

        $last_category = $row['Category']; // 'remember' the new category value
    }

echo "<tr><td><a href='".$row['URL']."'>" . $row['Title'] . "</a></td></tr>";

 

i really dont care for the tables i would rather just keep it simple and have no tables and just put the category names in bold and walla! just as long as i can have them display vertically rather then horizontally but when i take out the elements it throws me a syntax error    *scratching my head*

 

 

[PFMaBiSmAd]

To format your HTML output the way you want, I recommend some basic HTML tutorials - http://w3schools.com/html/default.asp

 

The following sections at the above link would be relevant -

HTML Headings

HTML Paragraphs

HTML Formatting

HTML Styles

 

HTML Tables

[shenagsandnags]

yea but shouldnt

 

echo " . $row['Category'] . "

 

echo "<a href='".$row['URL']."'>" . $row['Title'] . "</a>";

 

just give me:

 

Drama

Movie1

Movie2

 

Comedy

Movie3

 

Horror

Movie4

Movie5

 

?

 

 

believe it or not as dumb as i sound about php i use to know a little html back in the day. i do know how to create tables but i really dont care to have the tables for this code unless i absolutely have to (not sure if its required in php). just a simple listing without boarders and such as shown above would be just fine for me

 

[BlueSkyIS]

no. HTML line breaks are <br /> the line won't break until you tell it to.

[shenagsandnags]

ok i have just now noticed this

 

echo "<tr><td><a href='".$row['URL']."'>" . $row['Title'] . "</a></td></tr><br/>";

 

whenever i hover my mouse over one of the links, in my browsers status bar it will show it as if its in my root directory "http://www.mysite.com/root/www.google.com" instead of just "www.google.com".  and obviously when clicked on it wont work. through out this whole process i haven't touch the <a href"  line so im kinda clueless on this one. besides i need to add the target function in this but im not sure where and how to put it. i have tried,

 

echo "<tr><td><a href='".$row['URL']."' target="_blank">" . $row['Title'] . "</a></td></tr><br/>";

 

and a few other things but i just get a syntax error. help me get this figured out so i can seal this topic before they start charging me rent lol.