Creating New MySql Script
08-14-2005, 02:45 PM
|
Creating New MySql Script
|
Posts: 340
Name: Jon
Location: New York
|
Hello,
I am creating a new mysql script for my personal website. its function is to display links. what i am trying to do is have it were it reaches a maximum of 25 links per page then you have to click next. all sorted by date added. i am also trying to have it were a feild in the db is called activated. and if activated = 1 it shows the link and if =0 it does not. here is the basics i have written so far
PHP Code:
<?
require_once("config/mysql.php");
?>
<body>
<h1>Links</h1>
<table width="798" height="93" border="0">
<tr>
<td width="163" height="32">Site Name </td>
<td width="111">Link</td>
<td width="179">Clicks In </td>
<td width="189">Who Submited</td>
<td width="134">Date Added </td>
</tr>
<tr>
<?
$res = "SELECT * FROM links WHERE activated='1'";
$subres = mysql_query($res);
$i=0;
while ($i < $num) {
$autoid = $resualts['id'];
$sitename = $resualts['site_name'];
$link = $resualts['link'];
$in = $resualts['link_in'];
$submited = $resualts['submiter'];
$date_added = $resualts['date_submitted'];
$activated = $resualts['activated'];
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $sitename; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="<? echo $link; ?>"><? echo $sitename; ?></a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $in; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $submited; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $date_added; ?></font></td>
</tr>
<?
$i++;
}
echo "</table>";
?>
<p> </p>
</body>
</html>
I am stumped on how to pull the information from the db and have it go into those feilds then terminate when it hits 25.
__________________
AMW_Drizz
Dev Machine:: Apache 2.2.6 PHP 5.2.6 MySQL 5.1
|
|
|
|
|
08-14-2005, 04:31 PM
|
|
Posts: 100
Name: Nathen
Location: Casnewydd, De Cymru, UK
|
Quote:
|
Originally Posted by amw_drizz
Hello,
I am creating a new mysql script for my personal website. its function is to display links. what i am trying to do is have it were it reaches a maximum of 25 links per page then you have to click next. all sorted by date added. i am also trying to have it were a feild in the db is called activated. and if activated = 1 it shows the link and if =0 it does not. here is the basics i have written so far
PHP Code:
<?
require_once("config/mysql.php");
?>
<body>
<h1>Links</h1>
<table width="798" height="93" border="0">
<tr>
<td width="163" height="32">Site Name </td>
<td width="111">Link</td>
<td width="179">Clicks In </td>
<td width="189">Who Submited</td>
<td width="134">Date Added </td>
</tr>
<tr>
<?
$res = "SELECT * FROM links WHERE activated='1'";
$subres = mysql_query($res);
$i=0;
while ($i < $num) {
$autoid = $resualts['id'];
$sitename = $resualts['site_name'];
$link = $resualts['link'];
$in = $resualts['link_in'];
$submited = $resualts['submiter'];
$date_added = $resualts['date_submitted'];
$activated = $resualts['activated'];
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $sitename; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="<? echo $link; ?>"><? echo $sitename; ?></a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $in; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $submited; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $date_added; ?></font></td>
</tr>
<?
$i++;
}
echo "</table>";
?>
<p> </p>
</body>
</html>
I am stumped on how to pull the information from the db and have it go into those feilds then terminate when it hits 25. |
i believe that u have your varible wrong....
PHP Code:
<?
require_once("config/mysql.php");
?>
<body>
<h1>Links</h1>
<table width="798" height="93" border="0">
<tr>
<td width="163" height="32">Site Name </td>
<td width="111">Link</td>
<td width="179">Clicks In </td>
<td width="189">Who Submited</td>
<td width="134">Date Added </td>
</tr>
<tr>
<?
$res = "SELECT * FROM links WHERE activated='1'";
$subres = mysql_query($res);
$i=0;
while ($i < $num) {
$autoid = $subres['id'];
$sitename = $subres['site_name'];
$link = $subres['link'];
$in = $subres['link_in'];
$submited = $subres['submiter'];
$date_added = $subres['date_submitted'];
$activated = $subres['activated'];
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $sitename; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="<? echo $link; ?>"><? echo $sitename; ?></a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $in; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $submited; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $date_added; ?></font></td>
</tr>
<?
$i++;
}
echo "</table>";
?>
<p> </p>
</body>
</html>
if not then instead of $subres try $res, if u still have errors, post the errors up
__________________
Please login or register to view this content. Registration is FREE
Quote:
|
- "I've often wondered how MS can say that other software "isn't compatible" with MS when MS stuff isn't even compatible with other MS stuff" - quote from Daily Thing.com
|
|
|
|
|
|
08-14-2005, 05:26 PM
|
|
Posts: 217
Location: UK.Lancashire(true)
|
"SELECT * FROM links WHERE activated = 1 LIMIT 0, 25";
this limits the results to a max of 25. is that what you was asking?
|
|
|
|
|
08-14-2005, 06:42 PM
|
|
Posts: 340
Name: Jon
Location: New York
|
Quote:
|
Originally Posted by asm
"SELECT * FROM links WHERE activated = 1 LIMIT 0, 25";
this limits the results to a max of 25. is that what you was asking?
|
That is some of it.
I am still trying to figure out how to display more on a 2nd page.
Ie user clicks next and next will spawn the same page but 26-50, instead of 0-25
but the link would be like ?p=links&startrow=25
i am still working on this. as it may be harder but let it enrich my php learning experiance
Also how would i restrice direct access to the file. ie people must use the home page for access and not directly
__________________
AMW_Drizz
Dev Machine:: Apache 2.2.6 PHP 5.2.6 MySQL 5.1
Last edited by amw_drizz; 08-14-2005 at 06:53 PM..
|
|
|
|
|
08-14-2005, 06:45 PM
|
|
Posts: 217
Location: UK.Lancashire(true)
|
i see. what you are looking for is called pagination. give us a minute and i will find you an open source class
what about this. looks a good starting point but is not a class. there are some open source classes out there if you look though. the code in this one looks nice and simple 
Last edited by asm; 08-14-2005 at 06:49 PM..
|
|
|
|
|
08-14-2005, 06:59 PM
|
|
Posts: 340
Name: Jon
Location: New York
|
what is a open source class????
nice and simple eh. thats my intention
__________________
AMW_Drizz
Dev Machine:: Apache 2.2.6 PHP 5.2.6 MySQL 5.1
|
|
|
|
|
08-14-2005, 07:04 PM
|
|
Posts: 217
Location: UK.Lancashire(true)
|
sorry. it is.... well the open source bit it just means you can use it or alter it at your will and the class bit is......well it is far to complicated to worry about lol (maybe someone else would like to answer that)
|
|
|
|
|
08-14-2005, 07:11 PM
|
|
Posts: 340
Name: Jon
Location: New York
|
so how would i directly restric access to a file with php?
__________________
AMW_Drizz
Dev Machine:: Apache 2.2.6 PHP 5.2.6 MySQL 5.1
|
|
|
|
|
08-14-2005, 07:25 PM
|
|
Posts: 62
|
Here is a fixed version of your code.
PHP Code:
<?
require_once("config/mysql.php");
?>
<body>
<h1>Links</h1>
<table width="798" height="93" border="0">
<tr>
<td width="163" height="32">Site Name </td>
<td width="111">Link</td>
<td width="179">Clicks In </td>
<td width="189">Who Submited</td>
<td width="134">Date Added </td>
</tr>
<tr>
<?
$res = "SELECT *
FROM `link`
WHERE `activated` = '1'
LIMIT 0,25";
$subres = @mysql_query($res);
if(!$subres){ die("MySQL Error! " . mysql_error()); }
$i=0;
while ($resualts = @mysql_fetch_array($subres)) {
$autoid = $resualts['id'];
$sitename = $resualts['site_name'];
$link = $resualts['link'];
$in = $resualts['link_in'];
$submited = $resualts['submiter'];
$date_added = $resualts['date_submitted'];
$activated = $resualts['activated'];
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><?=$sitename?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="<?=$link?>"><?=$sitename?></a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?=$in; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?=$submited?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?=$date_added?></font></td>
</tr>
<?
$i++;
}
?>
<br>
</body>
</html>
That should work fine, though untested. |
|
|
|
|
08-14-2005, 07:41 PM
|
|
Posts: 340
Name: Jon
Location: New York
|
this is a version i got working with alternating row colours
PHP Code:
<?
require_once("config/mysql.php");
?>
<body>
<h1>Links</h1>
<table width="798" height="93" border="0">
<tr>
<td width="163" height="32">Site Name </td>
<td width="111">Link</td>
<td width="179">Clicks In </td>
<td width="189">Who Submited</td>
<td width="134">Date Added </td>
</tr>
<tr>
<?
$color1 = "#003333";
$color2 = "#330000";
$startrow = $_GET['startrow'];
if ($statrow == 25){
$limit = '25,50';
}
if ($startrow == 50){
$limit = '50,75';
}
if ($startrow == 75){
$limit ='75-100';
}
$res = "SELECT * FROM links WHERE activated = 1 LIMIT 0,25";
$subres = mysql_query($res) or die(mysql_error());
$num = mysql_num_rows($subres) or die(mysql_error());
$i=0;
while ($row = mysql_fetch_array($subres)) {
$id = $row['id'];
$sitename = $row['site_name'];
$link = $row['link'];
$in = $row['link_in'];
$submited = $row['submiter'];
$date_added = $row['date_submitted'];
$activated = $row['activated'];
$row_color = ($i % 2) ? $color1 : $color2;
echo "<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'>$sitename</font></td>
<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'><a href='redirect.php?id= $id'>$sitename</a></font></td>
<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'>$in</font></td>
<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'>$submited</font></td>
<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'>$date_added</font></td>
</tr>";
$i++;
}
echo "</table>";
?>
<p> </p>
</body>
</html>
I am still tyring to get it to work so after record 25 some one clicks next and it will show 26-50
__________________
AMW_Drizz
Dev Machine:: Apache 2.2.6 PHP 5.2.6 MySQL 5.1
|
|
|
|
|
08-14-2005, 08:22 PM
|
|
Posts: 217
Location: UK.Lancashire(true)
|
i would pass an argument into the page so when someone clicks to go the page a variable called $startnumber carries the limit number ie
somepage.php?startnumber=1
and then on the actual page you would have something like
PHP Code:
$myLimit = 25;
if (isset($_GET['startnumber'])
{
$startnumber = $_GET['startnumber'])
$endnumber = $startnumber + $myLimit;
}
$res = "SELECT * FROM links WHERE activated = 1 LIMIT $startnumber, $endnumber";
later when building the link to to reload the page
PHP Code:
//add one to $endnumber then pass to startnumber
$startnumber = ++$endnumber;
//next build a url same as before except
$aURL = "somepage.php?startnumber=" . $startnumber;
hope this helps cos i am making it up as i go along.
Last edited by asm; 08-14-2005 at 08:24 PM..
|
|
|
|
|
08-15-2005, 12:40 AM
|
|
Posts: 62
|
This would work for what youve specified and without the hardcoded $limit things.
PHP Code:
<?
require_once("config/mysql.php");
?>
<body>
<h1>Links</h1>
<table width="798" height="93" border="0">
<tr>
<td width="163" height="32">Site Name </td>
<td width="111">Link</td>
<td width="179">Clicks In </td>
<td width="189">Who Submited</td>
<td width="134">Date Added </td>
</tr>
<tr>
<?
$color1 = "#003333";
$color2 = "#330000";
$startrow = $_GET['startrow'];
if(is_numeric($startrow)&&($startrow>0)){
$endrow = $startrow + 25;
$limit = $startrow.",".$endrow;
}else{
$limit = "0,25";
}
$res = "SELECT * FROM links WHERE activated = 1 LIMIT ".$limit."";
$subres = mysql_query($res) or die(mysql_error());
$num = mysql_num_rows($subres) or die(mysql_error());
$i=0;
while ($row = mysql_fetch_array($subres)) {
$id = $row['id'];
$sitename = $row['site_name'];
$link = $row['link'];
$in = $row['link_in'];
$submited = $row['submiter'];
$date_added = $row['date_submitted'];
$activated = $row['activated'];
$row_color = ($i % 2) ? $color1 : $color2;
echo "<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'>$sitename</font></td>
<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'><a href='redirect.php?id= $id'>$sitename</a></font></td>
<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'>$in</font></td>
<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'>$submited</font></td>
<td bgcolor=$row_color><font face='Arial, Helvetica, sans-serif'>$date_added</font></td>
</tr>";
$i++;
}
echo "</table>";
?>
<p> </p>
</body>
</html>
This checks whether $_GET['startrow'] is numeric and greater than 0, if it is then it sets the start limit to startrow, and the end limit to startrow + 25.
I noticed in your code you have
PHP Code:
$num = mysql_num_rows($subres) or die(mysql_error());
You dont actually need that tbh, you can just use your startrow as a guide to figure out how many links its showing. |
|
|
|
|
« Reply to Creating New MySql Script
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
