[RESOLVED] Doesn't understand how to use MySQL arrays and then echo parts of it in order.

god0fgod

I'm making an article system to allow people to add content to my website and make it easier for me as well.

I've made a script that places data into a table with the fields id (auto_increment), title, Email address, date, body, general_cat, funny_cat, help_cat, quiz_cat, chat_cat, game_cat, name and username. I've also made a script that echos an article due to the id given. ex:

http://www.godofgod.co.uk/newssub/view_article.php?id=2

It works fine how I want it to. However I need to create a script that will show links to the articles. I want to echo links to all the articles where general_cat is 1 (A category for my articles) then funny_cat and so on under subtitles to help organise the articles. I want only 10 links for each category where newest articles are shown first (newer ids). I want the link to go to http://www.godofgod.co.uk/newssub?view_article.php?id=$id and the link to be shown as the articles title.

I've tried to do this but I'm too new to php to succeed. This is an important part as this will show the newest articles. Please explain what I'll need to do with understanding I know very little at the moment. Thanks for any help.

If you need any more information please tell me.

igorlopez

I am assuming that the same article can be of different category's at the same time otherwise your table should have one field named category of an enum type whit all your category's in it.
Anyway back to your question,
1) You need to query your db and extract all articles for one category.
2) The list of articles should be sorted with newest first
3) Build the string with the link and proper id from the article.

Maybe something in the line of:
(Assuming you have connected to the DB earlier)

	$categorys = array('general_cat', 'funny_cat', 'help_cat', 'quiz_cat', 'chat_cat', 'game_cat');
 	for($ix=0; $ix<6; $ix++) {
 	  echo "Add subtitling here for : " . $categorys[$ix];
 	  $query = "SELECT id, date FROM table  ORDER BY date DESC WHERE '$categorys[$ix]'=1";
 	  $result= mysql_query($query);
 	  $count=0;
 	  while($count < 10 && $row = mysql_fetch_assoc($result)) {
 	    $count++;
 	    $id = $row['id'];  // I just don't know how to handle ' within ' within "....
 	    // Here you need to add the rest of the html tags and so forth but you get the idea
 	    echo "http://www.godofgod.co.uk/newssub?view_article.php?id='$id'";
 	  }
 	}

Note to myself... Test Test and Test....

god0fgod

Thanks for that. The "ORDER BY" in the query is what I needed most as I was unsure on how to do that.

while($count < 10 && $row = mysql_fetch_assoc($array)) {

With that part where do I add the $row = mysql_fetch_assoc($array)? Don't I use $result in that function. Also whats the difference between mysql_fetch_assoc() and mysql_fetch_row()?

Oh and I am allowing articles to have more than 1 category.

Oh and don't arrays need a $ before them?

igorlopez

Yep, a lot of error in my post :o but you spotted them which is good.
I have edited the post and corrected the errors.
Hopefully none is left. The best way is to test in real environment, i just wrote the code down since i am to lazy to set it up here....

You should use mysql_fetch_assoc since it makes it more robust than mysql_fetch_row with respect to where the columns are populated.

Since you can have more than 1 category per article your DB schema is fine.

I have adjusted the above code for that.

god0fgod

Thanks for the 2nd reply. I managed to get to this:

$datacat = array('general_cat', 'funny_cat', 'help_cat', 'quiz_cat', 'chat_cat', 'game_cat'); 
$categorys = array('General', 'Funny', 'Help', 'Quiz', 'Chat', 'Game'); 
    for($ix=0; $ix<6; $ix++) { 
      echo $categorys[$ix] . "</br>"; 
      $query = "SELECT id, date, title FROM articles ORDER BY date DESC WHERE $datacat[$ix]=1"; 
      $result = mysql_query($query); 
      $count = 0; 
      while($count < 5 && $row = mysql_fetch_assoc($result)){ 
        $count++; 
        $id = $row['id'];  // I just don't know how to handle ' within ' within ".... 
        // Here you need to add the rest of the html tags and so forth but you get the idea 
        echo "<a href=\"http://www.godofgod.co.uk/newssub?view_article.php?id=" . $id . "\"><font face=\"arial\">" . $row['title'] . "</font></a></br>"; 
      } 
    }

From the script - http://www.godofgod.co.uk/newssub/articles.php - there is an error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/godofgod/public_html/newssub/articles.php on line 12

Same with fetch_row. fetch_row worked on my view_article script so I don't know what could be wrong on this one.

igorlopez

Now I am testing on my own computer and there are some problems.

First you need to switch the ORDER BY and WHERE statements,

$query = "SELECT id, date, title FROM articles WHERE $datacat[$ix]=1 ORDER BY date DESC ";

I got this working for one query but the next query did not return anything.

I'll be back.

OK it appears to be working as expected.
Could it be so that the error shows up when the query did not result in any records?

god0fgod

It works fine with me - http://www.godofgod.co.uk/newssub/articles.php

I'm just going to add more articles to see the effect. After this I'll finnish the script by adding all the HTML and will add features like the ability to sort by views (need to create a script that will handle the views) etc.

igorlopez

Good,

Don't forget to mark the thread as RESOLVED

god0fgod

New problem. When adding another row to the database I realised I need to collect information from a multidimensional array. I changed the script for this and now it has gone crazy. Also I used mysgl_fetch_array so I have a numerical array.

$datacat = array('general_cat', 'funny_cat', 'help_cat', 'quiz_cat', 'chat_cat', 'game_cat'); 
$categorys = array('General', 'Funny', 'Help related', 'Quizzes', 'Chatbots', 'Video game related'); 
    for($ix=0; $ix<6; $ix++) { 
      echo "<font face=\"arial\" size=\"3\"><u><b>" . $categorys[$ix] . ":</b></u></font></br>"; 
      $query = "SELECT id, title FROM articles WHERE $datacat[$ix]=1 ORDER BY date DESC"; 
      $result = mysql_query($query); 
      $count = 0; 
      while($count < 5 && $row = mysql_fetch_array($result)){
        $id = $row[0][$count];
        $title = $row[1][$count];
        // Here you need to add the rest of the html tags and so forth but you get the idea 
        echo "<a href=\"http://www.godofgod.co.uk/newssub/view_article.php?id=" . $id . "\"><font face=\"arial\">" . $title . "</font></a></br>";
        $count++;  

      }
    echo "</br>"; 
    }

What happened here I do not know - http://www.godofgod.co.uk/newssub/articles.php

igorlopez

I don't think so.

How did it look like when you were using mysql_fetch_assoc($result) with several rows??

If you add more rows to the DB and the result from the query is as you say multidimensional (it is two dimensional in the same way you would see it in the D😎
the functions used to extract the data from each row will maintain an internal pointer assuring that each call to the function will fetch the next 'row' which has as many items as you have select fields in the query (or all columns if you use *).
That is one reason to use $row as a result variable from the fetch functions since that is exactly what you will get.

Back to fetch array versus fetch associative.

The query in it self will sort the columns in the same order as you specify them when selecting which columns to get, but, when you select all columns they will come in the order the table has them and it is therefore better to use the associative fetch and index by column name instead of position since a change to table layout would break the script.

The part in the code with the counter: $count was there to make that you got at most 10 articles per subtitle and it would stop earlier.

This is probably what is breaking your current code.