Ordering

frankobi

I want the result of my query to be in alphabetical order, but I am having a problem figuring out where to place the “order by” command. Please help me. Here is my code:

<?php
include("connection.inc");
?>
<html>
<head>
<title>Detail Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link type="text/css" rel="stylesheet" href="styles.css">
</head>
10.
<body>
<?php
$rs = @mysql_select_db("lhsnycne_mydatabase",$conn) or die("error connecting 14. to the database");
15.
#using the wildcard (%) character in the last half of the statement allows users to 17. search with only the first few letters of the name
#another wildcard is placed between the first ' and " to allow total matching
searching
$sql = "SELECT * FROM novels2 WHERE id = " .$id = $_GET['id'];
$rs = mysql_query($sql, $conn);
?>
<h3>Here are the details....</h3>
<?php
$row = mysql_fetch_array($rs);
26.
echo("<p>Name: " . $row["fname"] . " " . $row["lname"] . "<br>");
echo("Title: " . $row["title"] . "<br>");
echo("Genre: " . $row["genre"] . "<br>");
echo("Information: " . $row["info"] . "<br>");
?>
<a href=<?php echo ($row["url_1"]);?> target="_blank"><?php echo
($row["url_1_description"] . "<br>"); ?></a>
34.
<?php
echo("Year Published: " .$row["year"] . "<br>");
echo("Date Added: " .$row["dte"] . "</p>");
?>
39.
<br>
<a href="http://lhsnyc.net/library/novels2/search01.php">Home</a>
</body>
</html>

BooRadLey

Line 20:

$sql = "SELECT * FROM novels2 WHERE id = ". $_GET['id'] ." ORDER BY title";

just change title with whatever you want to order it by alphabetically.

Hope this helps,

Brad

BooRadLey

I just noticed that you don't really need an order by clause if you are selecting one story. If you searched the database for a string then it would be useful, but for returning a single result I don't understand why you need it.

Brad

frankobi

I am sorry I posted the wrong code. Here is the code with which I need help. I need to know where to place the "order by" clause.

Thanks.

<?php
include("connection.inc");
?>
<html>
<head>
<title>Search novels</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link type="text/css" rel="stylesheet" href="styles.css">
</head>

<body>

<?php
$rs = mysql_select_db("lhsnycne_mydatabase",$conn) or die("error connecting to the database");

#using the wildcard (%) character in the last half of the statement allows users to search with only the first few letters of the name
#another wildcard could be placed between the first ' and " to allow total matching searching for letters between the first and last letters
$sql = "SELECT id,fname,lname FROM novels2 WHERE lname LIKE '" . $search_lname = $_POST['search_lname']."%';";


echo ($sql) . "<br>";

$rs = mysql_query($sql, $conn);

$total_results = mysql_num_rows($rs);

<h3>Here are the results...</h3>

<?php

if ($total_results > 1)
	{
		echo ("<p>You have found $total_results Records</p>");
	}

if ($total_results == 0)
	{
		echo ("<p>There are no records matching that name</p>");
	}

if ($total_results == 1)
	{
		echo ("<p>You have found $total_results Record</p>");
	}

<?php
$names=1;
while ($row = mysql_fetch_array($rs))
{
echo ("<p>$names. " . $row["fname"] . " " . $row["lname"] . "<br>");
$names=$names+1;
echo ("<a href='detail.php?id=" . $row["id"] . "'> See Details</a></p>");
}
?>
</body>
</html>

BooRadLey

$sql = "SELECT id,fname,lname FROM novels2 WHERE lname LIKE '" . $_POST['search_lname']. "%' ORDER BY lname";

Brad

frankobi

Brad,

Thank you very much. Everything now works perfectly.

frankobi

Thanks again for the help with my earlier inquiry. I am now able to see the result of my query as show below:

Name: Chinua Achebe
Title: Things Fall Apart
Genre: Fiction
Information:

Date Added: 0000-00-00

My problem now is how to present the results of my query in a tabular form, as shown below:

Name Title Genre Inform Date
Achebe Things 'Apart Fiction 0000-00-00

Could you please help me with the lines of code that will make that possible? Here is what I have tried, but no data was displayed.

<?php
include("connection.inc");
?>
<html>
<head>
<title>Detail Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link type="text/css" rel="stylesheet" href="styles.css">
</head>

<body>
<?php
$rs = @mysql_select_db("lhsnycne_mydatabase",$conn) or die("error connecting to the database");

#using the wildcard (%) character in the last half of the statement allows users to search with only the first few letters of the name
#another wildcard is placed between the first ' and " to allow total matching searching
$sql = "SELECT * FROM novels2 WHERE id = " .$id = $_GET['id'];
$rs = mysql_query($sql, $conn);

$list = "<table border=\1"\ cellpadding=\"2\">";
$list.="<tr><th>Name</th>;
$list.="<th>Title</th>;
$list.="<th>Genre</th>;
$list.="<th>Information</th>;
$list.="<th>Date</th>;

while ($row= mysql_fetch_array ($rs) )
{
$list .= "<tr>";
$list .= "<td>".$row["lname"] ."</td>";
$list .= "<td>".$row["title"] ."</td>";
$list .= "<td>".$row["genre"] ."</td>";
$list .= "<td>".$row["info"] ."</td>";
$list .= "<td>".$row["dte"] ."</td>";
$list .= "</tr>";
}
$list .= "<table>";
echo($list);

<h3>Here are the details....</h3>
<?php
$row = mysql_fetch_array($rs);

	echo("<p>Name: " . $row["fname"] . "&nbsp;" . $row["lname"] . "<br>");
	echo("Title: " . $row["title"] . "<br>");
	echo("Genre: " . $row["genre"] . "<br>");
	echo("Information: " . $row["info"] . "<br>");

?>
<a href=<?php echo ($row["url_1"]);?> target="_blank"><?php echo ($row["url_1_description"] . "<br>"); ?></a>

	<?php
	#echo("Year Published: " .$row["year"] . "<br>");
	echo("Date Added: " .$row["dte"] . "</p>");
?>

BooRadLey

I notice right away that your use of the WHERE clause is wrong and that is probably why you get no data returned:

this:

$sql = "SELECT * FROM novels2 WHERE id = " .$id = $_GET['id'];

should be this:

$sql = "SELECT * FROM novels2 WHERE id = " .$_GET['id'];

Try that out and reply with what you get.

Brad

PS - also add a </tr> after your last </th> and make <table> into </table> at the end after the loop.

frankobi

Brad,

Thanks again. It worked! Here is the link to the Web page: http://lhsnyc.net/library/novels2/search01.php.

And here is the code as I revised it:

======================
<?php
include("connection.inc");
?>
<html>
<head>
<title>Detail Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link type="text/css" rel="stylesheet" href="styles.css">
</head>

<body>
<?php
$rs = @mysql_select_db("lhsnycne_mydatabase",$conn) or die("error connecting to the database");

#using the wildcard (%) character in the last half of the statement allows users to search with only the first few letters of the name
#another wildcard is placed between the first ' and " to allow total matching searching
$sql = "SELECT * FROM novels2 WHERE id = " .$_GET['id'];
$rs = mysql_query($sql, $conn);

$list = "<table border=\"1\" cellpadding=\"2\">";
$list.="<tr><th>First Name</th>";
$list.="<th>Last Name</th>";
$list.="<th>Title</th>";
$list.="<th>Genre</th>";
$list.="<th>Information</th>";
$list.="<th>Date</th></tr>";

while ($row= mysql_fetch_array ($rs) )
{
$list .= "<tr>";
$list .= "<td>".$row["fname"] ."</td>";
$list .= "<td>".$row["lname"] ."</td>";
$list .= "<td>".$row["title"] ."</td>";
$list .= "<td>".$row["genre"] ."</td>";
$list .= "<td>".$row["info"] ."</td>";
$list .= "<td>".$row["dte"] ."</td>";
$list .= "</tr>";
}
$list .= "</table>";

echo ("Here are the details...");

echo($list);

?>

<p><a href="http://lhsnyc.net/library/novels2/search02.php">Back to the list</a></p>
<p><a href="http://lhsnyc.net/library/novels2/search01.php">To Novels Home Page</a></p>
</body>

</html>

Thanks.

Frank

BooRadLey

Great! I'm glad I could help out, the page looks like it's working well, feel free to ask for help anytime 😃

Brad

frankobi

You have helped me a great deal. My next project is to have my students retrieve their personal information from my database, when they enter their id numbers or their passwords. A rough example would be to modify what I have with the novels database so that an author would enter a password to see his/her records.

I have not started this yet, but if you have any ideas at this time please free to share them.

Thank you very much.

-Frank

frankobi

Please help me make the data in the "info" field a hyperlink. Please see lines 26 and 36.

Thanks in advance.

==========================

<?php
include("connection.inc");
?>
<html>
<head>
<title>Detail Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link type="text/css" rel="stylesheet" href="styles.css">
</head>
10.
<body>
<?php
$rs = @mysql_select_db("lhsnycne_mydatabase",$conn) or die("error connecting to the database");
14.
#using the wildcard (%) character in the last half of the statement allows users to search with only the first few letters of the name
#another wildcard is placed between the first ' and " to allow total matching searching
$sql = "SELECT * FROM novels2 WHERE id = " .$_GET['id'];
$rs = mysql_query($sql, $conn);
19.
20.
$list = "<table border=\"1\" cellpadding=\"2\">";
$list.="<tr><th>First Name</th>";
$list.="<th>Last Name</th>";
$list.="<th>Title</th>";
$list.="<th>Genre</th>";
$list.="<th>Information</th>";
$list.="<th>Date</th></tr>";
28.
while ($row= mysql_fetch_array ($rs) )
{
$list .= "<tr>";
$list .= "<td>".$row["fname"] ."</td>";
$list .= "<td>".$row["lname"] ."</td>";
$list .= "<td>".$row["title"] ."</td>";
$list .= "<td>".$row["genre"] ."</td>";
$list .= "<td>".$row["info"] ."</td>";
$list .= "<td>".$row["dte"] ."</td>";
$list .= "</tr>";
}
$list .= "</table>";
41.
echo ("Here are the details...");
43.
echo($list);
45.
46.
?>
48.
<p><a href="http://lhsnyc.net/library/novels2/search02.php">Back to the list</a></p>
<p><a href="http://lhsnyc.net/library/novels2/search01.php">To Novels Home Page</a></p>
</body>
</html>

frankobi

Please help me make the data in the "info" field a hyperlink. Please see lines 26 and 36.

Thanks in advance.

==========================

<?php
include("connection.inc");
?>
<html>
<head>
<title>Detail Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link type="text/css" rel="stylesheet" href="styles.css">
</head>
10.
<body>
<?php
$rs = @mysql_select_db("lhsnycne_mydatabase",$conn) or die("error connecting to the database");
14.
#using the wildcard (%) character in the last half of the statement allows users to search with only the first few letters of the name
#another wildcard is placed between the first ' and " to allow total matching searching
$sql = "SELECT * FROM novels2 WHERE id = " .$_GET['id'];
$rs = mysql_query($sql, $conn);
19.
20.
$list = "<table border=\"1\" cellpadding=\"2\">";
$list.="<tr><th>First Name</th>";
$list.="<th>Last Name</th>";
$list.="<th>Title</th>";
$list.="<th>Genre</th>";
$list.="<th>Information</th>";
$list.="<th>Date</th></tr>";
28.
while ($row= mysql_fetch_array ($rs) )
{
$list .= "<tr>";
$list .= "<td>".$row["fname"] ."</td>";
$list .= "<td>".$row["lname"] ."</td>";
$list .= "<td>".$row["title"] ."</td>";
$list .= "<td>".$row["genre"] ."</td>";
$list .= "<td>".$row["info"] ."</td>";
$list .= "<td>".$row["dte"] ."</td>";
$list .= "</tr>";
}
$list .= "</table>";
41.
echo ("Here are the details...");
43.
echo($list);
45.
46.
?>
48.
<p><a href="http://lhsnyc.net/library/novels2/search02.php">Back to the list</a></p>
<p><a href="http://lhsnyc.net/library/novels2/search01.php">To Novels Home Page</a></p>
</body>
</html>

BooRadLey

$list .= "<td><a href=\"mypage.php?id=".$row['id']."\">".$row["info"] ."</a></td>";

I just made a generic querystring using the id, you can change it to whatever you need.

Hope this helps,

Brad

dnast

Is "info" a URL? If so:

36. $list .= "<td><a href=\"".$row["info"]."\">".$row["info"]."</a></td>";

frankobi

Yes; info is a URL. Thank you very much. It works.

Would you know how to make the Web page open as a new window? In other words, where do I place the "target+_blank" clause?

Thanks

--Frank

frankobi

Thanks Brad. info is a URL. It works.

Would you know how to make the Web page open as a new window? In other words, where do I place the "target=_blank" clause?

Thanks

--Frank

diego25

target goes nside the <a> tag:

Something like

$list .= "<td><a href=\"mypage.php?id=".$row['id']."\" target=\"_blank\">".$row["info"] ."</a></td>";

laserlight

You would place it as you normally would, as an attribute of the <a> tag.

The PHP code doesnt 'interfere' with the clientside code.