Please, need help condensing this query

[Champak]

I know nothing about querying, but I hacked this query to display something from my database on one of my pages. I know this is way bigger than it should be and convoluted. Could someone help me to condense this query script and make it "proper"?

<table width="260"><tr>
<?php
/* Connecting, selecting database */
$link = mysql_connect(
"localhost", "user", "Pass") or die("Could not connect");
mysql_select_db("table") or die("Could not select database");

/* Performing SQL query */
$query = "SELECT Image FROM models_textpattern order by Posted desc limit 1";
$result = mysql_query($query) or die("Query failed");

/* Printing results in HTML */
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
foreach ($line as $col_value) { print "\t\t<td valign=top> <img src=".$col_value." align=left hspace=5px>\n"; }

/* Closing connection */
mysql_close($link);
?>
<?php
/* Connecting, selecting database */
$link = mysql_connect(
"localhost", "user", "pass") or die("Could not connect");
mysql_select_db("table") or die("Could not select database");

/* Performing SQL query */
$query = "SELECT Title FROM models_textpattern order by Posted desc limit 1";
$result = mysql_query($query) or die("Query failed");

/* Printing results in HTML */
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
foreach ($line as $col_value) { print "<font size=3><a href='http://www.XXXXXX.com/'>$col_value</a></font>"; }

/* Closing connection */
mysql_close($link);
?>
<br><br>
<?php
/* Connecting, selecting database */
$link = mysql_connect(
"localhost", "user", "pass") or die("Could not connect");
mysql_select_db("table") or die("Could not select database");

/* Performing SQL query */
$query = "SELECT Excerpt FROM models_textpattern order by Posted desc limit 1";
$result = mysql_query($query) or die("Query failed");

/* Printing results in HTML */
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
foreach ($line as $col_value) { print "<font size=2>$col_value</font></td>\n"; }

/* Closing connection */
mysql_close($link);
?>
</tr></table>

[Champak]

Also, if you notice, I'm querying three things from the database table and need to place them in a specific order in a specific way in the html table.

[Champak]

I got it down to this, I hope someone can get it down further for me:

<table width="260"><tr>
<?php
/* Connecting, selecting database */
$link = mysql_connect(
"localhost", "user", "pass") or die("Could not connect");
mysql_select_db("table") or die("Could not select database");

/* Performing SQL query */
$query1 = "SELECT Image FROM models_textpattern order by Posted desc limit 1";
$result1 = mysql_query($query1) or die("Query failed");
$query2 = "SELECT Title FROM models_textpattern order by Posted desc limit 1";
$result2 = mysql_query($query2) or die("Query failed");
$query3 = "SELECT Excerpt FROM models_textpattern order by Posted desc limit 1";
$result3 = mysql_query($query3) or die("Query failed");

/* Printing results in HTML */
while ($line1 = mysql_fetch_array($result1, MYSQL_ASSOC))
foreach ($line1 as $col_value1) { print "\t\t<td valign=top> <img src=".$col_value1." align=left hspace=5px>\n"; }
while ($line2 = mysql_fetch_array($result2, MYSQL_ASSOC))
foreach ($line2 as $col_value2) { print "<font size=3><a href='http://www.XXXXXX.com/models/'>$col_value2</a></font><br><br>"; }
while ($line3 = mysql_fetch_array($result3, MYSQL_ASSOC))
foreach ($line3 as $col_value3) { print "<font size=2>$col_value3</font></td>\n"; }

/* Closing connection */
mysql_close($link);
?>
</tr></table>

[Anyango]

$query1 = "SELECT Image FROM models_textpattern order by Posted desc limit 1";
$result1 = mysql_query($query1) or die("Query failed");
$query2 = "SELECT Title FROM models_textpattern order by Posted desc limit 1";
$result2 = mysql_query($query2) or die("Query failed");
$query3 = "SELECT Excerpt FROM models_textpattern order by Posted desc limit 1";
$result3 = mysql_query($query3) or die("Query failed");

instead of making and executing those three queries, to get three fields of same table you should simply write one query and get all those three fields back
like this

$query = "SELECT Image,Title,Excerpt,Posted FROM models_textpattern order by Posted desc limit 1";

execute that query and u get all those three values. ;)

P.S: i am not sure how can your queries be running and not resulting in an error because the oder By Field you have "Posted", that isnt in SELECT clause, AFAIK u cant sort something based on a fielf which u didnt even select

[Champak]

Thanks, I know I can query that way, but I didn't do it that way because I didn't know how to spit out the individual fields where I wanted. Could you show me that part?

[Anyango]

here u go

$Query="SELECT Image,Title,Excerpt,Posted FROM models_textpattern order by Posted desc limit 1";
$Result=mysql_query($Query) or die (mysql_error());
$RecordSet=mysql_fetch_array($Result);
$Image=$RecordSet['Image'];
$Title=$RecordSet['Title'];
$Excerpt=$RecordSet['Excerpt'];
$Posted=$RecordSet['Posted'];

now you can show your $Image,$Title,$Excerpt,$Posted variables seperately whereever you want em. ;)

[Anyango]

Another thing,

because in your query your Limit was set to 1 so at max obviously 1 record could come back that is why i didnt put in a loop to get values from all rows of the recordset, however if you ever need to perform this or any other query where number of records could be more then one, for that I use this

$Query="SELECT Image,Title,Excerpt,Posted FROM models_textpattern order by Posted desc limit 1";
$Result=mysql_query($Query) or die (mysql_error());
$RecordCount=mysql_num_rows($Result);

for($i=0;$i<$RecordCount;$i++)
{
$RecordSet=mysql_fetch_assoc($Result);
$Image=$RecordSet['Image'];
$Title=$RecordSet['Title'];
$Excerpt=$RecordSet['Excerpt'];
$Posted=$RecordSet['Posted'];

// echo your variables to html here.

}

[Champak]

Thanks for all your help. Works like a charm.