Old dude needs help with his database mess
Posted: Wed Nov 23, 2005 1:36 pm
First, hello and sorry for the huge and begging first post.
I'm a bit of a novice at most things web based but prefer to dabble with a bit HTML and PHP than watch Eastender's!
I've reached a pont where I have a reasonable MySQL database from which I can search, pull out and format the results. The next stage was to add some paging. This is when a google search found me a tutorial with script I hoped to adapt.
This worked great if I wanted to return all the results from my table, nicely formatted with paging. However, the moment I tried to adapt the search query I end up with all sorts of problems.
This is the code, its messy as I'm no spring chicken and I'm fairly new to this, but what I want to do is be more specific about the search.........
To do this I first want to add a search box (form) at the top of the page...
Then get the variable from it........
I then want to change the query to this which should check the data for a matching variable......
and I end up with this...
The resulting page I called mess.php!
Once I get something working I hope to do something usefull with what I learn for charity, sorry to play on the heart strings but its true.
Any help would be great, I don't do this for a living just so there's no urgency. I just hate the thought of being beaten by a bracket in the wrong place although I suspect its more than that.
Thanks if you can help if not thanks for reading and thinking about it.
I'm a bit of a novice at most things web based but prefer to dabble with a bit HTML and PHP than watch Eastender's!
I've reached a pont where I have a reasonable MySQL database from which I can search, pull out and format the results. The next stage was to add some paging. This is when a google search found me a tutorial with script I hoped to adapt.
This worked great if I wanted to return all the results from my table, nicely formatted with paging. However, the moment I tried to adapt the search query I end up with all sorts of problems.
This is the code, its messy as I'm no spring chicken and I'm fairly new to this, but what I want to do is be more specific about the search.........
Code: Select all
<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
$theQuery = mysql_db_query($database, "SELECT title FROM pictures");
$theRowsAmount = mysql_num_rows($theQuery);
$pgPageLength = 3;
$pgTotalPages = intval($theRowsAmount/$pgPageLength);
if ($theRowsAmount%$pgPageLength) {
$pgTotalPages++;
}
if ($gotoPage != ""){
if ($gotoPage == "1"){
$pgStartPoint = 0;
}else{
$pgStartPoint = ($pgPageLength * $gotoPage) - 3;
}
}else{
$pgStartPoint = 0;
}
$getDetails = mysql_db_query($database, "SELECT * FROM pictures ORDER BY id DESC LIMIT $pgStartPoint, $pgPageLength");
if(!($getDetails)) {
print "Unable to access database for myTable!";
}
?>
<?
$ShownPage = $gotoPage;
if ($ShownPage != ""){
}else{
$ShownPage = 1;
}
if ($ShownPage == 1){
}else{
$PrevPage = $ShownPage - 1;
?>
<a href="picsearch3.php?gotoPage=<? echo $PrevPage; ?>">Prev</a>
<?
}
if ($pgTotalPages >= 11){
$pgFirstLoads = $ShownPage - 5;
$pgLastLoads = $ShownPage + 4;
if ($pgLastLoads >= $pgTotalPages){
$pgLastLoads = $pgTotalPages;
$pgLastDots = "";
}else{
$pgLastDots = " | ... | <a href=\"picsearch3.php?gotoPage=$pgTotalPages\">Last</a>";
}
if ($pgFirstLoads <= 1){
$pgFirstLoads = 0;
$pgFirstDots = "";
}else{
$pgFirstDots = " | <a href=\"picsearch3.php?gotoPage=1\">First</a> | ...";
}
echo $pgFirstDots;
for($i = $pgFirstLoads; $i < $pgLastLoads; $i++) {
$currPage = $i + 1;
if ($currPage != $ShownPage){
echo " | <a href=\"picsearch3.php?gotoPage=$currPage\">$currPage</a>";
}else{
echo " | <strong>$currPage</strong>";
}
}
echo $pgLastDots;
}else{
for($i = 0; $i < $pgTotalPages; $i++) {
$currPage = $i + 1;
if ($currPage != $ShownPage){
echo " <a href=\"picsearch3.php?gotoPage=$currPage\">$currPage</a>";
}else{
echo " <strong>$currPage</strong>";
}
}
}
if ($ShownPage == $pgTotalPages){
}else{
$NextPage = $ShownPage + 1;
?>
| <a href="picsearch3.php?gotoPage=<? echo $NextPage; ?>">Next</a>
<?
}
?>
</td>
</tr>
<tr>
<td class=database><table id="results table" width="95%" border="0" cellspacing="0" cellpadding="4">
<?
while ($row = mysql_fetch_object($getDetails)) {
$getID = $row->id;
$getTitle = $row->title;
$getCollection = $row->collection;
$getInfo = $row->info;
$getDate = $row->date;
$getDescription = $row->description;
$getImage = $row->image;
$getBigimage = $row->bigimage;
?>
<tr>
<td class=database width="31%"> </td>
<td class=database width="42%"><b>
Description:</b></td>
<td class=database width="27%">
<p align="center"><b> <? echo $getTitle; ?> </b></td>
</tr>
<tr>
<td class=database width="31%"><b>Title:</font></b> <? echo $getTitle; ?> </td>
<td class=database width="42%" rowspan="5">
<? echo $getDescription; ?> </td>
<td class=database width="27%" rowspan="4">
<p align="center">
<a target="_blank" href="http://www.**********/**********/data/upimages/<? echo $getBigimage;?> ">
<img border="0" src="http://www.**********/**********/data/upimages/<? echo $getImage;?> "></a> </td>
</tr>
<tr>
<td class=database width="31%"><b>
Collection:</b> <? echo $getCollection; ?></td>
</tr>
<tr>
<td class=database width="31%"><b>More info:</b>
<a href="http://www.**********/<? echo $getInfo; ?>">Website</a></td>
</tr>
<tr>
<td class=database width="31%"><b>Date:</b>
<? echo $getDate; ?></td>
</tr>
<tr>
<td class=database width="31%"> </td>
<td class=database width="27%">
<p align="center"><b>Image:</b>
<? echo $getID; ?> </td>
</tr>
<tr>
<td class=database width="100%" colspan="3"><hr></td>
</tr>
<?
}
mysql_close();
?>
</table></td>
</tr>
<tr>
<td class=database id="paging bottom here">
<?
$ShownPage = $gotoPage;
if ($ShownPage != ""){
}else{
$ShownPage = 1;
}
if ($ShownPage == 1){
}else{
$PrevPage = $ShownPage - 1;
?>
<a href="picsearch3.php?gotoPage=<? echo $PrevPage; ?>">Prev</a>
<?
}
if ($pgTotalPages >= 11){
$pgFirstLoads = $ShownPage - 5;
$pgLastLoads = $ShownPage + 4;
if ($pgLastLoads >= $pgTotalPages){
$pgLastLoads = $pgTotalPages;
$pgLastDots = "";
}else{
$pgLastDots = " | ... | <a href=\"picsearch3.php?gotoPage=$pgTotalPages\">Last</a>";
}
if ($pgFirstLoads <= 1){
$pgFirstLoads = 0;
$pgFirstDots = "";
}else{
$pgFirstDots = " | <a href=\"picsearch3.php?gotoPage=1\">First</a> | ...";
}
echo $pgFirstDots;
for($i = $pgFirstLoads; $i < $pgLastLoads; $i++) {
$currPage = $i + 1;
if ($currPage != $ShownPage){
echo " | <a href=\"picsearch3.php?gotoPage=$currPage\">$currPage</a>";
}else{
echo " | <strong>$currPage</strong>";
}
}
echo $pgLastDots;
}else{
for($i = 0; $i < $pgTotalPages; $i++) {
$currPage = $i + 1;
if ($currPage != $ShownPage){
echo " <a href=\"picsearch3.php?gotoPage=$currPage\">$currPage</a>";
}else{
echo " <strong>$currPage</strong>";
}
}
}
if ($ShownPage == $pgTotalPages){
}else{
$NextPage = $ShownPage + 1;
?>
| <a href="picsearch3.php?gotoPage=<? echo $NextPage; ?>">Next</a>
<?
}
?>Code: Select all
<form name="form" action="test.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>Code: Select all
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variableCode: Select all
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM pictures WHERE title LIKE \"%$trimmed%\" OR description LIKE \"%$trimmed%\" OR collection LIKE \"%$trimmed%\" OR image LIKE \"%$trimmed%\" ORDER BY id";
$result=mysql_query($query);Code: Select all
<form name="form" action="test.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
$theQuery = mysql_db_query($database, "SELECT title FROM pictures");
$theRowsAmount = mysql_num_rows($theQuery);
$pgPageLength = 3;
$pgTotalPages = intval($theRowsAmount/$pgPageLength);
if ($theRowsAmount%$pgPageLength) {
$pgTotalPages++;
}
if ($gotoPage != ""){
if ($gotoPage == "1"){
$pgStartPoint = 0;
}else{
$pgStartPoint = ($pgPageLength * $gotoPage) - 3;
}
}else{
$pgStartPoint = 0;
}
$getDetails = mysql_db_query($database, "SELECT * FROM pictures WHERE title LIKE \"%$trimmed%\" OR
description LIKE \"%$trimmed%\" OR collection LIKE \"%$trimmed%\" OR image LIKE \"%$trimmed%\"
DESC LIMIT $pgStartPoint, $pgPageLength");
if(!($getDetails)) {
print "Unable to access database for myTable!";
}
?>
<?
$ShownPage = $gotoPage;
if ($ShownPage != ""){
}else{
$ShownPage = 1;
}
if ($ShownPage == 1){
}else{
$PrevPage = $ShownPage - 1;
?>
<a href="picsearch3.php?gotoPage=<? echo $PrevPage; ?>">Prev</a>
<?
}
if ($pgTotalPages >= 11){
$pgFirstLoads = $ShownPage - 5;
$pgLastLoads = $ShownPage + 4;
if ($pgLastLoads >= $pgTotalPages){
$pgLastLoads = $pgTotalPages;
$pgLastDots = "";
}else{
$pgLastDots = " | ... | <a href=\"picsearch3.php?gotoPage=$pgTotalPages\">Last</a>";
}
if ($pgFirstLoads <= 1){
$pgFirstLoads = 0;
$pgFirstDots = "";
}else{
$pgFirstDots = " | <a href=\"picsearch3.php?gotoPage=1\">First</a> | ...";
}
echo $pgFirstDots;
for($i = $pgFirstLoads; $i < $pgLastLoads; $i++) {
$currPage = $i + 1;
if ($currPage != $ShownPage){
echo " | <a href=\"picsearch3.php?gotoPage=$currPage\">$currPage</a>";
}else{
echo " | <strong>$currPage</strong>";
}
}
echo $pgLastDots;
}else{
for($i = 0; $i < $pgTotalPages; $i++) {
$currPage = $i + 1;
if ($currPage != $ShownPage){
echo " <a href=\"picsearch3.php?gotoPage=$currPage\">$currPage</a>";
}else{
echo " <strong>$currPage</strong>";
}
}
}
if ($ShownPage == $pgTotalPages){
}else{
$NextPage = $ShownPage + 1;
?>
| <a href="picsearch3.php?gotoPage=<? echo $NextPage; ?>">Next</a>
<?
}
?>
</td>
</tr>
<tr>
<td class=database><table id="results table" width="95%" border="0" cellspacing="0" cellpadding="4">
<?
while ($row = mysql_fetch_object($getDetails)) {
$getID = $row->id;
$getTitle = $row->title;
$getCollection = $row->collection;
$getInfo = $row->info;
$getDate = $row->date;
$getDescription = $row->description;
$getImage = $row->image;
$getBigimage = $row->bigimage;
?>
<tr>
<td class=database width="31%"> </td>
<td class=database width="42%"><b>
Description:</b></td>
<td class=database width="27%">
<p align="center"><b> <? echo $getTitle; ?> </b></td>
</tr>
<tr>
<td class=database width="31%"><b>Title:</font></b> <? echo $getTitle; ?> </td>
<td class=database width="42%" rowspan="5">
<? echo $getDescription; ?> </td>
<td class=database width="27%" rowspan="4">
<p align="center">
<a target="_blank" href="http://www.**********/**********/data/upimages/<? echo $getBigimage;?> ">
<img border="0" src="http://www.**********/**********/data/upimages/<? echo $getImage;?> "></a> </td>
</tr>
<tr>
<td class=database width="31%"><b>
Collection:</b> <? echo $getCollection; ?></td>
</tr>
<tr>
<td class=database width="31%"><b>More info:</b>
<a href="http://www.**********/<? echo $getInfo; ?>">Website</a></td>
</tr>
<tr>
<td class=database width="31%"><b>Date:</b>
<? echo $getDate; ?></td>
</tr>
<tr>
<td class=database width="31%"> </td>
<td class=database width="27%">
<p align="center"><b>Image:</b>
<? echo $getID; ?> </td>
</tr>
<tr>
<td class=database width="100%" colspan="3"><hr></td>
</tr>
<?
}
mysql_close();
?>
</table></td>
</tr>
<tr>
<td class=database id="paging bottom here">
<?
$ShownPage = $gotoPage;
if ($ShownPage != ""){
}else{
$ShownPage = 1;
}
if ($ShownPage == 1){
}else{
$PrevPage = $ShownPage - 1;
?>
<a href="picsearch3.php?gotoPage=<? echo $PrevPage; ?>">Prev</a>
<?
}
if ($pgTotalPages >= 11){
$pgFirstLoads = $ShownPage - 5;
$pgLastLoads = $ShownPage + 4;
if ($pgLastLoads >= $pgTotalPages){
$pgLastLoads = $pgTotalPages;
$pgLastDots = "";
}else{
$pgLastDots = " | ... | <a href=\"picsearch3.php?gotoPage=$pgTotalPages\">Last</a>";
}
if ($pgFirstLoads <= 1){
$pgFirstLoads = 0;
$pgFirstDots = "";
}else{
$pgFirstDots = " | <a href=\"picsearch3.php?gotoPage=1\">First</a> | ...";
}
echo $pgFirstDots;
for($i = $pgFirstLoads; $i < $pgLastLoads; $i++) {
$currPage = $i + 1;
if ($currPage != $ShownPage){
echo " | <a href=\"picsearch3.php?gotoPage=$currPage\">$currPage</a>";
}else{
echo " | <strong>$currPage</strong>";
}
}
echo $pgLastDots;
}else{
for($i = 0; $i < $pgTotalPages; $i++) {
$currPage = $i + 1;
if ($currPage != $ShownPage){
echo " <a href=\"picsearch3.php?gotoPage=$currPage\">$currPage</a>";
}else{
echo " <strong>$currPage</strong>";
}
}
}
if ($ShownPage == $pgTotalPages){
}else{
$NextPage = $ShownPage + 1;
?>
| <a href="picsearch3.php?gotoPage=<? echo $NextPage; ?>">Next</a>
<?
}
?>Once I get something working I hope to do something usefull with what I learn for charity, sorry to play on the heart strings but its true.
Any help would be great, I don't do this for a living just so there's no urgency. I just hate the thought of being beaten by a bracket in the wrong place although I suspect its more than that.
Thanks if you can help if not thanks for reading and thinking about it.