Jump to content

quick help


shenagsandnags

Recommended Posts

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!

 

 

 

 

Link to comment
Share on other sites

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.  :)

Link to comment
Share on other sites

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.

 

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

<?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!

Link to comment
Share on other sites

<?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! :)

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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%

Link to comment
Share on other sites

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!

 

Link to comment
Share on other sites

<?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

 

Link to comment
Share on other sites

<?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

Link to comment
Share on other sites

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);
?>

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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);
?>

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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*

 

 

Link to comment
Share on other sites

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 :)

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.