PHP Retrieve from DB

[dlannetts]

Hello, l want to retrieve information from a database,, l have a section for it to load but there is two entries (for now) l want it to pick a random one and load it, how can l do this? in my html file l have Name: <?php echo $latest_deal_name; ?><br/> and so on, this loads from and external file in that external file l want it to retrieve the info from a database and a column named 'name' PLUS pick a random one from all the entries and display it, how can l do thsi?

[nileshgr]

Can you please paste some of your code ? The code of the section and any existing code in the external file ?

[dlannetts]

Can you please paste some of your code ? The code of the section and any existing code in the external file ?

The external file is huge, it is full of php to suit other things, for this topic but l have no php in it to get the info, if l did l wouldn't have made this topic... as said before but in my home page file l have <?php echo $latest_deal_name; ?> this links to the external file, l think l need something like: (but l am not sure, any ideas?

$data = mysql_query("SELECT * FROM mydbtest1")
or die(mysql_error());
$info = mysql_fetch_array( $data );

$latest_deal_name=$info['name'];

[nileshgr]

The code you posted is what you need actually, but if you're outputting only the name, then use

SELECT name from table

in order to reduce processing cost.
In your code you need to use mysql_fetch_assoc instead of mysql_fetch_array.
Next, for random row,
I assume you have some column called id which is the primary key and stores numbers. So to get a random name, use this code:

$res = mysql_query('SELECT MIN(id), MAX(id) from table') or die(mysql_error());
$low = mysql_fetch_field($res, 0);
$high = mysql_fetch_field($res, 1);
do {
$rnum = mt_rand($low, $high);
$res = mysql_query('SELECT * from table WHERE id = ' . $rnum) or die(mysql_error());
// here too, if you have only one-two fields to use, then specify them instead of * to reduce processing cost
$info = mysql_fetch_assoc($res);
}
while(count($info) != 1);

Then your can use $info and assign its indices to your variables and use it.

[waseem12]

Using PHP you can run a MySQL SELECT query to fetch the data out of the database. You have several options in fetching information from MySQL. PHP provide several functions for this. mysql_fetch_object(), mysql_fetch_array().

$query=select * from table_name;
 $query_result=mysql_query($query) or die("Sorry".mysql_error());
  while($row=mysql_fetch_object($query_result)){
    echo $row->name;
 }

[dlannetts]

.... ....

So ... using your example l have this:

$res = mysql_query('SELECT MIN(id), MAX(id) from latest_deal') or die(mysql_error());
$low = mysql_fetch_field($res, 0);
$high = mysql_fetch_field($res, 1);
do {

$rnum = mt_rand($low, $high);
$res = mysql_query('SELECT * from latest_deal WHERE id = ' . $rnum) or die(mysql_error());
$info = mysql_fetch_assoc($res);
}
while(count($info) != 1);

And my original code:

$data = mysql_query("SELECT * FROM latest_deal")
or die(mysql_error());
$info = mysql_fetch_array( $data );

$latest_deal_name=$info['name'];
$latest_deal_description=$info['description'];
$latest_deal_picture=$info['picture'];
$latest_deal_price=$info['price'];
$latest_deal_finish_date=$info['finish_date'];

Does this all look right? and how do l putting these together, thanks itech... :)

[nileshgr]

Everything looks OK to me. Can't say anything unless it is tried actually :)

At the part where you're assigning values to variables, you can simplify the code using this:

extract($info, EXTR_PREFIX_ALL, 'latest_deal');

[dlannetts]

Everything looks OK to me. Can't say anything unless it is tried actually :)

At the part where you're assigning values to variables, you can simplify the code using this:

extract($info, EXTR_PREFIX_ALL, 'latest_deal');

oh yep, so l put it together as l had it in the last post and l got this:

Warning: mt_rand() expects parameter 1 to be long, object given in C:\wamp\www\minimax2\temp.php on line 60
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

that would be with this:

$rnum = mt_rand($low, $high);

[nileshgr]

Uh I'm so dumb. Was confusing mysql_fetch_field with something else.
Replace $low and $high in the variable assignment so that it looks like this:

$low = array_shift(mysql_fetch_row($res));
$high = array_pop(mysql_fetch_row($res));

I think this should solve it.

[vaibhav1983]

Easiest I would suggest is...

$query="SELECT name FROM Table ORDER BY RAND()";
$result=mysql_query($query) or die(mysql_error());
//The following code loopes through the entries retreived
while($row=mysql_fetch_array($result))
{
$name=mysql_escape_string(trim($row));
}

If you want to extract 20 entries from 100 set of entries and choose them randomly you can use

$query="SELECT name FROM Table ORDER BY RAND() LIMIT 20";
$result=mysql_query($query) or die(mysql_error());
//The following code loopes through the entries retreived
while($row=mysql_fetch_array($result))
{
$name=mysql_escape_string(trim($row));
}

[nileshgr]

@vaibhav, that's a good idea, but performance issue: http://www.phpbuilder.com/board/showthread.php?t=10338930