Anyone know of an easy way to call single rows with one query?

Nomaad

I have a site I am building and have ran into a issue. I would like to do one main query of the DB then instead of looping through with virtually control of placement, be able to call the UI and row name to get just that cell to display. But do that without having to write 500 gazillion select statements. 🙂 Does that make any sense?

I was trying to set the result in an array but can still only get the first row data to come in, I need some way to tell it to get the results from field 'model' from row '2' and so on . . .

Thanks in advance.

frost110

SELECT model FROM table WHERE id = 2

Something like that? Which you can turn into like this.


$sqlQU = mysql_query("SELECT model FROM table ORDER BY model");

while ($row = mysql_fetch_assoc($sqlQU)) {
      print "Row: " . $row['model'] . "<br />";
}

If not show us some current code. Hard to do anything without looking at code.

--FrosT

Nomaad

$query = "SELECT * FROM venting WHERE ID = '2'";
$result = mysql_query($query) or die('Error, query failed: ' . mysql_error());
While ($myrow = mysql_fetch_row($result))
{
$id = $myrow[1];
$productname = $myrow[5];
$grouptitle = $myrow[6];
$groupsubtitle = $myrow[7];
$contenttitle1 = $myrow[8];
$content1 = $myrow[9];
$contenttitle2 = $myrow[10];
$content2 = $myrow[11];
$subtitle1 = $myrow[12];
$subtitle2 = $myrow[13];
$subtitle3 = $myrow[14];
$bartitle1 = $myrow[15];
$bartitle2 = $myrow[16];
$bartitle3 = $myrow[17];
$reference = $myrow[18];
$bulletcontent1 = $myrow[19];
$bulletcontent2 = $myrow[20];

echo str_replace(Chr(13), "<br>", ("<h1>$grouptitle $groupsubtitle</h1>
<h3>$contenttitle1</h3>
$content1 <BR><BR>
<h3>$bartitle1</h3>
$content2<BR /><BR />
<em>$reference</em>
"));
?>
This works fine if I wanted to call each row individually ( WHERE ID = '2') I was trying to get away with not having to call each row thereby repeating the entire query statement each time I need a new row. I thought since the intial query actually stores the entire contents there had to be a way to access that at will instead of a table build style loop . . .

A little background, this is a product site that contains well over 100 pages. All taken from a database we have connected with a quark xpress catalog. The tables in MySQL were designed around the way the catalog had to be pulled into the doc. Which poses difficulties for me on the web side. They have now come back and said they want each product category page to contain all the pages of the catalog that pertain to it. I was planning on just doing includes since the pages were pretty much finished as above. The problem, in IE using anchor tags to link to an include breaks the pages, so I will have to do the entire long page.

Nomaad

Here it is in all it's glory:

//set the array var
$results = array();

//query the db
$query_rs_multi_views = "SELECT * FROM di_specs_chart";
$result = mysql_query($query_rs_multi_views) or die(mysql_error());

//calling the total rows and fields
$totalRows_rs_multi_views = mysql_num_rows($result);
$totalFields_rs_multi_views = mysql_num_fields($result);

//start the loop
for ( $i = 0; $i < $totalRows_rs_multi_views; ++$i )
//make the array associative
$results[$i] = mysql_fetch_array($result);

//now I can access at will any bit of data in the table off one select statement
echo $results[5]['model'];

PRETTY COOL . . . huh?!?

Roger_Ramjet

If you know the record number, as you seem to do when using the numeric index on your results array (echo $results[5]['model']😉, then you can just use mysql data seek to access them from the original query results without first having to itterate through the whole resultset.


//query the db
$query_rs_multi_views = "SELECT * FROM di_specs_chart";
$result = mysql_query($query_rs_multi_views) or die(mysql_error());

// move the cursor to the 5th record
mysql_data_seek($result, 4);
$row = mysql_fetch_array($result);
echo $row['model'];

// move the cursor to the 3rd record
mysql_data_seek($result, 2);
$row = mysql_fetch_array($result);
echo $row['model'];

// move the cursor to the 205th record
mysql_data_seek($result, 204);
$row = mysql_fetch_array($result);
echo $row['model'];
[code=php]

Far more efficient unless you are going to access a high percentage of the rows.