a few php and mysql questions

[amylisa]

hey I'm very new to this and really dont know much about mysql yet, I've tried looking for a tutorial but i cant seem to find one that explains these , I'll explain what i'm trying to do and hopfully someone here can help me

 

1- I'm trying to write a code that will get all the data from a mysql table and display it in an html table the data is stored in the mysql table as a single number , instead of the html showing the number I want to change it to a block of color to fill the table cell. eg. if the mysql result is 1 the table cell will be filled with blue.

 

question -  How do I actually get this to display as a color instead of a number?

 

2- I'm trying to write a script that will select the newest 10 values (from the 10 bottom rows)from a mysql table and store them in an array.

I have no idea on this one , i dont know how to select only the 10 newest rows and i cant seem to get a script working to store them in an array , I keep getting errors or blank pages.

 

question - How do I make it select only the newest 10 values? and how do I store the results in an array?

 

I would really appreciate  any help with this or a link to a tutorial that would help me.

Thank you in advance

[trq]

1. You would need to use some sort of parser to switch values from a number to a colour. eg;

 

<?php

  $val = 3;
  switch ($val) {
    case 1:
      $ret = 'black';
      break;
    case 2:
      $ret = 'blue';
      break;
    case 3:
      $ret = 'white';
      break;
  }

  echo $ret;

?>

 

2 You will need to store your records allong with a timestamp, then simply sort by that timestamp. eg;

 

SELECT foo FROM tbl ORDER BY stamp DESC LIMIT 10;

[chet139]

Hey I was at your stage not long ago (few months) it would be hard just to give code and assume you will understand it.

 

Try Using w3schols php tutorials they will take you through the basics. Thats what I did.

 

But...

 

1. basically you will need a .php/.html page.

 

2. SQL statement that that gets what you want.

 

3. Get results and use while loop

 

4. then output into table

 

I hope this helps... or I may be completely of topic ...getting the newest values... I would be guessing...but you would have to have some sort of date maybe or the largest ID of that table....depending on how you input rows....

[uniflare]

1. ok this is quite simple, use if statements after you get your results, with a loop. eg:

 

<?php

// define your colors to numbers here
$Color_Array = Array( "1" => "#0000FF", "2" => "#00FF00", "3" => "#FF0000" ); // (1=blue,2=green,3=red)

// connect to mysql

$results = mysql_query("SELECT `color` FROM `color_table`"); // Query the db, select the field "color" from all rows in table "color_table"
$resultset = mysql_fetch_array($results,MYSQL_NUM); // Put all results into a numbered array. (numbered is easier to count for the "for" loop)

// $resultset = array("1","2","1","3","3","1"); This is an example of what the result set should look like.

$output = "<table width="100%"><tr>"; // Start of table

// Loop through each row, add a Column with the color specified.
$x = count($resultset);
For($i=0;$i<$x;$i++){
  $output .= "<td color=\"".$Color_Array[$resultset[$i]]."\"> </td>\n";
}

$output .= "</tr></table>"; // End the row/table tag

echo($output); // echo all the results

?>

---

 

2, im no sql expert but ill try formulate a query that will work...

[uniflare]

ok to get last 10 results you may need 2 queries if you dont have some sort of id, or timestamp field;

 

 

<?php
// connect to mysql
mysql_connect("host","user","pass");

// Query to count number of rows
$query = "SELECT count(*) FROM `table`";

// submit query
$result = mysql_query($query);

// Get result (row count)
$rowcount = mysql_result($result); // this line *might* not work, if not try: mysql_result($result,0)

// take away 10 from total rows to get starting row, or "offset".
$offset = $rowcount - 10;

// Make the next query to get last 10 results
$query2 = "SELECT * FROM `table` LIMIT ".$offset .",$rowcount";

// store results in an array
$results = mysql_fetch_array($query2);

// Echo the results array.
print_r($results);
?>

 

hope this helps,

[moon 111]

No, I don't think a time stamp is required. You can simply use the 'id' row (oops! I always use an id row I don't know if anyone else uses it - it's a good idea to use).

 

Also since I would think he would want it in ascending order he can use the TOP command.

 

SELECT TOP 10 foo FROM tbl

 

I'm not sure if that is the correct use. I'll check in a sec

 

EDIT: Yup it is

[moon 111]

OK no sorry huge mistake ignore my post.

[amylisa]

thanks all ^^ love this forum When I was googling for answers I just kept finding threads where no one replied  then 2 days later someone would reply with "use google". ><

 

@thorp and uniflare - thanks Will try implementing that code into my scripts i have wrote already

 

@chet139 - Already looking on w3schools and working my way through the tutorials. I'm ok ish with the actual php (or can atleast normally find the info on how to do what i want on the php) I just dont understand the mySQL part, still trying to find a good tutorial the explains all the feaures in more depth.

 

@moon111 - thanks anyways I'v put an id on the colomn I want the data from but I have no idea what it does, guess I should go google that