Page 1 of 1

A bit of Newb help please (php / SQL)

Posted: Sun Feb 26, 2012 3:53 pm
by MiniMonty
Hi all,

I'm trying to build a very basic news system.
I've got a table in SQL that has a few columns:
headline, article, pic1, date, number.


They're all varchar and all hold text except "number" which increments per entry and "date" which is set by "now()" in the form.
I want to pull out the data in a certain way and so far I've got this far:

Code: Select all

include_once "../scripts/connect_to_mysql.php";
$result = mysql_query("SELECT * FROM articles ORDER BY number DESC");
while($row = mysql_fetch_array($result))
  {
  echo $row['number'] .$row['headline'] .$row['article'] ."news/images/" .$row['pic1'] . "<br>";
  }
This prints the whole lot the the screen, in descending order, as you'd expect.

Now I want to be a bit snazzier and assign each entry a variable name so the data on row 1 would end up as:
$headline1= (whatever is in the table field "headline" on row 1).
$article1= (whatever is in the table field "article" on row 1)
$picture1= (whatever is in the table field "pic1" on row 1)
etc etc etc

I want to do this for the first thirty entries and I'm guessing there's a loop of some sort I can use to do it but after
a couple of hours of trying I'm turning here for help :?

I want eventually to turn the whole lot into flashVars if that helps but just getting the basics working would be a big step forward right now.

All and any tips / advice / links / code very much appreciated.

Best wishes
Monty

Re: A bit of Newb help please (php / SQL)

Posted: Sun Feb 26, 2012 4:07 pm
by social_experiment
MiniMonty wrote:I want to do this for the first thirty entries and I'm guessing there's a loop of some sort I can use
You're already using a while loop (which is working); you only have to add the fields into the table, populate them and call the respective field names when you echo to the browser

I reread the post and i suspect this might not be what you have in mind; Could you elaborate on this part (which seems to be what you want to achieve)
MiniMonty wrote:$headline1= (whatever is in the table field "headline" on row 1).
$article1= (whatever is in the table field "article" on row 1)
$picture1= (whatever is in the table field "pic1" on row 1)

Re: A bit of Newb help please (php / SQL)

Posted: Sun Feb 26, 2012 5:49 pm
by MiniMonty
Hi social,
You're right - the second part of your post concerns what I want to achieve.

Pictures speak a thousand words so here is one:
Image

The pic is a screen shot of the contents of row 1 on the table "articles".
I want to pull out the entries and give each one a variable name of it's own so:
$headline1="Man Bites Dog";
$article1="Ouch, that must hurt the dog";
$pic1="news/images/img_sd87f6sdf.jpg";

then the same for the most recently added 30 rows:
$headline2="blah blah blah";
$article2="blah blah blah";
$pic2="news/images/blah blah blah.jpg";

$headline3="blah blah blah";
$article3="blah blah blah";
$pic3="news/images/blah blah blah.jpg";

Any help much appreciated.
Sunday night here (In England) and I'm supposed to have this working on Monday morning) !!

Best wishes
Monty

Re: A bit of Newb help please (php / SQL)

Posted: Sun Feb 26, 2012 6:42 pm
by litebearer

Re: A bit of Newb help please (php / SQL)

Posted: Mon Feb 27, 2012 5:18 am
by social_experiment
You could use a for loop; you can place the values inside an array with each iteration of the for loop.

Code: Select all

<?php
 $qry = mysql_query("SELECT COUNT(`id`) FROM articles");
 $ary = mysql_fetch_array($qry);
 // this returns the amount of records
 $total = $ary[0];

 $result = mysql_query("SELECT `number`, `headline`, `article`, `pic1` FROM `articles` ORDER BY `number` DESC");
 while ($row = mysql_fetch_array($results)) {
    for ($i = 0; $i<=$total; $i++) {
       $information[$i]['number'] = $row['number'];
       $information[$i]['headline'] = $row['headline'];
       $information[$i]['article'] = $row['article']; 
       $information[$i]['pic1'] = $row['pic1'];
    }
 }
 
 // use this to test if any information is inside $information
 print_r($information);
?>
This will create a multi-dimensional array and you can access each array inside in the following manner $information[$x] where $x is the array you want.

Hth