Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Image upload database reference - image name row ID

Status
Not open for further replies.
JamesCliff

JamesCliff

Programmer
Feb 16, 2005
106
GB
Hi all,

I have the following table structure:-

CREATE TABLE `sales` (
`SaleID` int(5) NOT NULL auto_increment,
`Title` varchar(255) NOT NULL default '',
`Image_Ref` varchar(255) NOT NULL default '',
`Description` text NOT NULL,
`Date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`SaleID`),
FULLTEXT KEY `Description` (`Description`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

I want to have a form in which i can fill in the fields for the database table above. And also select a picture to upload to a directory and automaticlly add a reference to the table (ive been told this is better than storing the image inside the table itself). I want it so when the image is uploaded to the directory the image reference is added to the database and the image name is changed to the specific row ID of the table.

How would i do this, can anyone help me because im stuck?

Thanks alot

Jim
 
Sort by date Sort by votes
tables are limited vehicles for controlling display.

a cludgy solution is to insert a row between each content row. in the blank row have only one table data element with the colspan attribute set to the number of columns in your table. insert an <hr> tag inside the table data element. eg
Code: 
<table>
<tr><td>foo</td><td>bar</td></tr>
<tr><td colspan="2"><hr /></td></tr>
<tr><td>foo</td><td>bar</td></tr>
</table>
 
Upvote 0 Downvote
Style your table with CSS. That way you can achieve just about any look you want. To put a line in between every row, you would just use border-bottom: 1px solid black; on <tr> for instance. But this is all an html rather than php question.
 
Upvote 0 Downvote
Right,

Im just looking through the script and webpage its on now, and basicly the more machines on it the longer its going to get, and as a result it could be slow on loading times and look un-professional with a never ending page lol.

So i was thinking is it possible to insert some code into that current script that limits the amount of record displayed on one page, so for example we could have a maximum of 10 records on each page, and then a next and back button on each page to go back to the previous or next set of records?

Would i need multiple php pages or could it be done on just that one by adding some code that does it?

This is my current script code:

Code: 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>

<body>
<div align="center">
  <table width="724">
    <!--DWLayoutTable-->
    <tr> 
      <td width="100%" height="261" valign="top"><div align="center">
          <br><p><font size="4">sales</font></p>
          <p>Please contact us for any further information or pictures regarding<br>
            a sales item in the list below.<br>
            <br>
            <a href="index.php?page=contact">Click here for contact information</a></p>
          <p>&nbsp;</p>
          <p> 
            <?


mysql_connect("127.0.0.1","test","") or die("Could not connect to SQL server!");
mysql_select_db("testdb") or die("Could not find database!");

$bgcolor='#FF0000';

echo "<table>";
echo "<tr bgcolor=$bgcolor>

      <td><strong>Photo</strong></td>
	  <td><strong>Make / Model</strong></td>
	  <td><strong>Description</strong></td>
	  <td><strong>Date Posted</strong></td>
	  <td><strong>Price</strong></td>
	  
	  </tr>";
	  
echo "<tr>";
	 
echo '<td colspan="5"><hr /></td>
		   
	  </tr>';

$table = "sales" ; 
$sql = "select `Image_Ref`, `Title`, `Description`, `Date`, `Price` from $table"; 
$rslt = mysql_query($sql);

while ($result = mysql_fetch_array($rslt))

{     

     echo "<tr>";
	 
     echo '<td><img src="photos/' . $result['Image_Ref'] . '" width="100" height="75"></td> ';
     echo '<td width="175">' . $result['Title'] . '</td>
	       <td width="250">' . $result['Description'] . '</td>
		   <td width="175">' . $result['Date'] . '</td>
		   <td width="100">' . $result['Price'] . '</td>
		   
		   </tr>';
		   
	 echo "<tr>";
	 
     echo '<td colspan="5"><hr /></td>
		   
		   </tr>';


}
echo "</table>";

          ?>
            <br>
          </p>
        </div></td>
    </tr>
  </table>
</div>
</body>
</html>

Is it possible to add some code to the code above to limit the amount of records to 10 per page, and then if theres more than 10 records create next and previous buttons so the user can goto the next page or previous page which is automaticlly populated with the next set of records? So basiclly i dont have to manually make a new page it can all be done through that one sales.php above with some extra code?

Thanks alot!

Jim
 
Upvote 0 Downvote
it is possible. the way to do it is add a "LIMIT " statement to the end of a query. the limit directive can take a start and a quantitive value. so LIMIT 5 would return the first 5 rows but LIMIT 5, 5 would return the 6th to 10th rows.

pass the offset and number value in the url. at the bottom of each page place a "next" link which points to the current page with the variables encoded in the link itself. pick the varialbes up using the $_GET superglobal and bung them into the query.

hope that helps.
Justin
 
Upvote 0 Downvote
lol it does help me yeh m8, however it sounds extremely complicated to pull off. Way above my knowledge range. Is that only way of doing it or is there any others?

Dont suppose u can mock up an example code can u m8?

Thanks

Jim
 
Upvote 0 Downvote
i think there is an FAQ on this.

below are two code snips. the first is some mock code to show how page offsets work.
the second is a quick piece of code to create a db table that can be used to try the first code.

Remember to fill in the variable parameters at the top of each codesnip before you use them.

APOLOGIES TO READERS - THESE CODE SNIPS ARE LONG.
Code: 
<?
//connect to the database
$database = "";
$host = "";
$username = "";
$password = "";
$num_display_records = 10; //this controls the number of records per page
$table = ""; //this is the database table name
$firsttime = true; //used to control echoing of fieldnames
$cnt = 0; //used as a counter for a colspan attribute later

mysql_pconnect($host, $username, $password) 
	or die ("cannot connect to database server. Error: ". mysql_error());
mysql_select_db($database)
	or die ("cannot select database. Error: ". mysql_error());
	
////end db connect ///////////

//handle the offsets

if (isset($_GET['offset']))
{
	$startnumber = $_GET['offset'];
}
else
{	
	$startnumber = 0;
}

//assemble the sql queries
// one to get the total number of records  and
// second to get just the number we want

$cntsql = "select count(*) as cnt from $table";
$sql = "SELECT * from $table LIMIT $num_display_records	OFFSET $startnumber";
echo "sql query is: $sql"; //for debug purposes
echo "<br /><br />";
$num_rows = mysql_result(mysql_query($cntsql),0); // this gives us the total number of rows

$results = mysql_query($sql)
	or die ("Unable to process query. Error is: " .mysql_error());  // this actually retrieves the records

///// we now have the dataset
// now calculate the number of pages
$num_pages = ceil ($num_rows/$num_display_records);// this calculates the total number of pages necessary (always round up)
$cur_page = $startnumber/$num_display_records; // need this for the page display
$stylename=".page_".$cur_page*$num_display_records;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
<title>Row Offset test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style type="text/css">
<?=$stylename?> {
border-color:red;
border-width:thin;
border-style:dotted;
}
.blue
{background-color:lightblue;}
.green
{background-color:lightgreen;}
</style>
</head>

<body>
<?
//start display of records
echo "<table border=\"1\">\r\n";
while ($row = mysql_fetch_assoc($results))
{

	if ($firsttime)
	{
		$rowclass="blue";
		$line1= "<tr>\r\n";
		$line2="<tr class=\"$rowclass\">\r\n";
		foreach ($row as $key=>$val)
		{
			$line1 .= "\t<td>$key</td>\r\n"; 
			$line2 .= "\t<td>$val</td>\r\n"; //displays the database results down the page rather than across
			$cnt++; //this counts the number of fields for use in the colspan attribute below
		}
		$line1 .="</tr>\r\n";
		$line2 .="</tr>\r\n";

		echo $line1;
		echo $line2;
		$firsttime = false;
	}
	else
	{
		if ($rowclass==="blue"){$rowclass="green";}else{$rowclass="blue";} //this shows the row colour change
		$line2="<tr class=\"$rowclass\">\r\n";
		foreach ($row as $key=>$val)
		{
			$line2 .= "\t<td>$val</td>\r\n"; //displays the database results down the page rather than across
		}
		$line2 .="</tr>\r\n";

		echo $line2;
	}
	echo "<tr></tr>\r\n"; //inserts a blank row for formatting
} //end of while loop

// all data is now displayed.
//now construct the page offset html
echo "<tr><td colspan=\"$cnt\" align=\"center\">Pages:\r\n";
$self = $_SERVER['PHP_SELF'];
for($i=0;$i<($num_pages*$num_display_records);$i=$i+$num_display_records)
{
	$page = $i/$num_display_records + 1; //makes sure that you can't have a page 0! 
	echo "\t<a class=\"page_$i\" href=\"$self?offset=$i\">$page</a>&nbsp;\r\n"; //this sequentially displays the page numbers. the id is necessary to pick up the style attribute
}
echo "</td></tr>\r\n";
echo "</table>";


?>
</body>
</html>

second code
Code: 
<?
$database = "";
$host = "";
$username = "";
$password = "";

mysql_pconnect($host, $username, $password) 
	or die ("cannot connect to database server. Error: ". mysql_error());
mysql_select_db($database)
	or die ("cannot select database. Error: ". mysql_error());

$createsql = "
CREATE TABLE `testdb` (
  `recordtextid` varchar(100) NOT NULL default '',
  `recordtext` varchar(100) NOT NULL default ''
) TYPE=MyISAM; ";

mysql_query($createsql)
	or die("cannot create database");
	
for ($i=0; $i<197;$i++)
{
	$id = "Record id $i";
	$text = "text for record $i";
	$sql = "insert into testdb set recordtextid = '$id', recordtext='$text'";	
	mysql_query($sql);
	}
?>
 
Upvote 0 Downvote
i've just noticed that i didn't implement the next and previous buttons you were actually asking for. hopefully the above code will be enough for you to work out how to do next and prev.
 
Upvote 0 Downvote
Ok thx for the detailed reply above m8.

Ive tried a script i found on phpfreaks, and ive implemented it into my code. It dosnt really work. A link for the script in action is found at bottom of this post so you can see what is going on. It does display 5 record entrys (i specified it to display 5 per page) but it dosnt display the links to other pages and theres a few errors on the page. Below is my script, can someone look through it for me and edit it if needs be or tell me what ive done wrong:

URL:
Code: 
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<?php

mysql_connect("127.0.0.1","jim11","") or die("Could not connect to SQL server!");
mysql_select_db("gbplantandmachinery") or die("Could not find database!");

    $limit          = 5;                
    $query_count    = "SELECT count(*) FROM sales";     
    $result_count   = mysql_query($query_count);     
    $totalrows      = mysql_num_rows($result_count); 
	
echo "<table>";
echo "<tr bgcolor=$bgcolor>

      <td><strong>Photo</strong></td>
	  <td><strong>Make / Model</strong></td>
	  <td><strong>Description</strong></td>
	  <td><strong>Date Posted</strong></td>
	  <td><strong>Price</strong></td>
	  
	  </tr>";
	  
echo "<tr>";
	 
echo '<td colspan="5"><hr /></td>
		   
	  </tr>';


    if(empty($page)){ 
        $page = 1; 
    } 
         
	$table = "sales" ;
    $limitvalue = $page * $limit - ($limit); 
    $sql  = "SELECT `Image_Ref`, `Title`, `Description`, `Date`, `Price` FROM $table LIMIT $limitvalue, $limit";         
    $rslt = mysql_query($sql); 

    if(mysql_num_rows($result) == 0){ 
        echo("Nothing to Display!"); 
    } 

while ($result = mysql_fetch_array($rslt))

{     

     echo "<tr>";
	 
     echo '<td><img src="photos/' . $result['Image_Ref'] . '" width="100" height="75"></td> ';
     echo '<td width="175">' . $result['Title'] . '</td>
	       <td width="250">' . $result['Description'] . '</td>
		   <td width="175">' . $result['Date'] . '</td>
		   <td width="100">' . $result['Price'] . '</td>
		   
		   </tr>';
		   
	 echo "<tr>";
	 
     echo '<td colspan="5"><hr /></td>
		   
		   </tr>';


}
echo "</table>";

    if($page != 1){ 
        $pageprev = $page--; 
         
        echo("<a href=\"$PHP_SELF&page=$pageprev\">PREV".$limit."</a>&nbsp;"); 
    }else{ 
        echo("PREV".$limit."&nbsp;"); 
    } 

    $numofpages = $totalrows / $limit; 
     
    for($i = 1; $i <= $numofpages; $i++){ 
        if($i == $page){ 
            echo($i."&nbsp;"); 
        }else{ 
            echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;"); 
        } 
    } 


    if(($totalrows % $limit) != 0){ 
        if($i == $page){ 
            echo($i."&nbsp;"); 
        }else{ 
            echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;"); 
        } 
    } 

    if(($totalrows - ($limit * $page)) > 0){ 
        $pagenext = $page++; 
          
        echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>"); 
    }else{ 
        echo("NEXT".$limit); 
    } 
     
    mysql_free_result($result); 

?>

</body>
</html>

You can see the above script in action at:

As you can see it isnt working right. If some 1 can repair it, i would be greatful.

Thanks alot

Jim
 
Upvote 0 Downvote
James

i've gone to the trouble of helping you with a script.

it would be polite perhaps to try and integrate that which one of us has helped you with (at your request) before brushing it off and asking us to help you fix someone else's work.

if you really want to use another code set then
(i) don't expect much help from those that you have previously asked to assist you;
(ii) at least evidence some desire to self-help: follow sleipnir214's FAQ on debugging and come back to the forum once you have done so, posting what you have done by way of debugging and what output you have received.

at a minimum you should look at
* the creation of your $limit_value variable.
* your $totalrows variable will ALWAYS return one as you have not formulated it properly.
* your $page variable assumes register_globals is set to on. this would be very unusual and contra-indicated for security purposes

i recommend you close this post and start a new thread with the results of your debugging exercise.
 
Upvote 0 Downvote
I tried that one before you posted yours jpadie. I appreciated all your help. But before I was to try yours I was wondering what I had done wrong to cause this one to fail, so that I could learn from my mistakes. I’m only a beginner at this and find it difficult to implement such a script.

Just for your self notification, I did not brush your script off at all, I haven’t even tried your script yet, and that was next in line. I'm learning from both your script and other scripts which I found, and from this trying to create a more efficient and customizable script.

For your information I have been self helping, and trying to learn new techniques. I’m grateful to all members of this forum for helping me achieve my goal, and I have used there code, all I did there was simply try to implement it into someone else’s code. I am quite happy with the code yourself and others have provided me with and therefore I am going to use that. I was only experimenting with other snippets of code and creating new code. I am sorry if that is a crime. Before you accuse me of brushing your work aside (thus making me sound an ignorant and unappreciative being) maybe you would like to wait and hear my story, as I described it above.

Again I thank you for your help and many others who have spent time to help me and shared there skills with me. It is appreciated.

If anyone needs any help in the near future feel free to contact me and ill try my best, just at those who have helped me have tried and satisfied me. I must apologise for my curious and experimental mind.

Thanks again

Jim
 
Upvote 0 Downvote
Hi guys

I have 1 more problem, i figured i might as well post it in this thread because you all know the background details of my database and scripts.

Im trying to get it so instead of displaying the picture in the script along with the title, description etc it displays a link on each record that has the caption "View Picture" and then when it is clicked the specific picture is opened in a new window. So instead of the picture in the table cell i would have a link instead.

Thanks

Jim
 
Upvote 0 Downvote
Jim

is this the issue that you have now posted a new thread on?

Justin
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

W
  • Locked
  • Question
how to send checked row data in php
Replies
0
Views
444
wiltang2004
W
PeDa
  • Locked
  • Question
prepend row to two dimensional array 1
Replies
4
Views
520
PeDa
PeDa
ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
850
ersatz
ersatz
Gerrit Broekhuis
  • Locked
  • Question
PHP form with captcha and file upload
Replies
4
Views
473
Gerrit Broekhuis
Gerrit Broekhuis
Brianfree
  • Locked
  • Question
split file name into different values
Replies
1
Views
310
spamjim
spamjim

Part and Inventory Search

Sponsor

Back
Top