Page 1 of 1

Old dude needs help with his database mess

Posted: Wed Nov 23, 2005 1:36 pm
by Siftersam
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.........

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;
?>
&nbsp;| <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%">&nbsp;</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%">&nbsp;</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;
?>
&nbsp;| <a href="picsearch3.php?gotoPage=<? echo $NextPage; ?>">Next</a>
<?
} 
?>
To do this I first want to add a search box (form) at the top of the page...

Code: Select all

<form name="form" action="test.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>
Then get the variable from it........

Code: Select all

// Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable
I then want to change the query to this which should check the data for a matching variable......

Code: 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);
and I end up with this...

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;
?>
&nbsp;| <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%">&nbsp;</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%">&nbsp;</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;
?>
&nbsp;| <a href="picsearch3.php?gotoPage=<? echo $NextPage; ?>">Next</a>
<?
} 
?>
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.