PHP and MySQL question

Kitty · December 23, 2006

Okay, I see people don't quite get my questions, so I don't get any good replies. Anyway, I have another question. It's really hard to explain, but I'll try.

I have a table in my MySQL database, called [B]layouts[/B]. To make it easier for you to understand, I took a [URL=http://"http://img162.imageshack.us/img162/2879/mysqlay4.jpg"]screenshot[/URL]. When I insert a new layout into that table, it looks like [URL=http://"http://img178.imageshack.us/img178/4358/mysql2eq2.jpg"]this[/URL]. Please, note that my links aren't page.html and file.zip, but IDs.

This is the code I'm using to show the layouts on my page:

[code=php:0]
<?php

//MySQL connection variables
$user="blah";
$host="localhost";
$password="blahblah";
$database="blahblah;
$connection = mysql_connect($host,$user,$password)
or die ("couldn't connect to server"); $db = mysql_select_db($database,$connection)
or die ("Couldn't select database");

//Which mySQL table to select from and in what order
$query = "select * from `layouts` order by id desc";

$result = mysql_query($query, $connection) or die ("Could not execute query : $query ." . mysql_error());
$rows = mysql_num_rows($result);
if ($rows=="0") { echo "No layouts found."; }

//Gets and assigns variables to array
while ($row=mysql_fetch_array($result)) {
$id=$row["id"];
$title=$row["title"];
$artist=$row["artist"];
$artisturl=$row["artisturl"];
$type=$row["type"];
$size=$row["size"];
$date=$row["date"];
$previewimage=$row["previewimage"];
$download=$row["download"];
$dcounter=$row["dcounter"];
$preview=$row["preview"];
$pcounter=$row["pcounter"];

//Prints out results
echo "<table id=\"box_top\"><tr>
<tr><td>
$title
</td>
</tr>
</table>

<table id=\"box\">
<tr>
<td valign=\"top\">
<img src=\"$previewimage\" width=\"160\" height=\"110\" border=\"0\" alt=\"\">
</td>
<td valign=\"top\" width=\"170\">
Added: $date
 Type: $type
 Size: $size
 Designer: <a href=\"$artisturl\" target=\"_blank\">$artist</a>
 Previews: HERE
 Downloads: HERE
 
<a href=\"$preview\">Preview</a>   <a href=\"$download\">Download</a>
</td>
</tr>
</table> ";
}

?>[/code]

Now here's the question, how can I display the number of the downloads/previews where it says HERE? Sorry, I'm not good at PHP, but I guess I need a code that would read the link's ID (that I entered in the database, i.e fclick/fclick.php?[B]47[/B]) and that would count how many times that ID has been clicked. Maybe, I need something like <?php echo $ID; ?> or something?

I really hope someone can help me, I explained as good as I could. :)

utexas_pjm · December 23, 2006

How are you incrementing preview and pcounter?

Kitty · December 23, 2006

The thing is I don't have any other codes than this one and I have been told that I need a code that would add 1 to the database if the link is clicked, but I don't know the code for it. Can you help me? :)

kamasheto · December 23, 2006

[code]<?php
$user="blah";
$host="localhost";
$password="blahblah";
$database="blahblah;
mysql_connect($host,$user,$password);
mysql_select_db($database);
$query = mysql_query("select * from `layouts` order by id desc");
$numb = mysql_num_rows($result);
if (!$numb)
{
echo "No layouts found.";
} else {
while ($row = mysql_fetch_array($query))
{
$id=$row["id"];
$title=$row["title"];
$artist=$row["artist"];
$artisturl=$row["artisturl"];
$type=$row["type"];
$size=$row["size"];
$date=$row["date"];
$previewimage=$row["previewimage"];
$download=$row["download"];
$dcounter=$row["dcounter"];
$preview=$row["preview"] + 1;
$pcounter=$row["pcounter"] + 1;

mysql_query("UPDATE `layouts` SET `preview`=".$preview.",`pcounter`=".$pcounter." WHERE id=".$id);
echo "<table id='box_top'><tr>
<tr>
<td>
$title
</td>
</tr>
</table>

<table id='box'>
<tr>
<td valign='top'>
<img src='$previewimage' width='160' height='110' border='0' alt=''>
</td>
<td valign='top' width='170'>
Added: $date 
Type: $type 
Size: $size 
Designer: <a href='$artisturl' target='_blank'>$artist</a> 
Previews: HERE 
Downloads: HERE 

<a href='$preview'>Preview</a>   <a href='$download'>Download</a>
</td>
</tr>
</table>
 ";
}
}
?>[/code]

Kitty · December 24, 2006

Thank you for the code, kamasheto, but it doesn't work. It makes my links look like this: mysite.com/1 and it also adds the same number to all layouts.

kamasheto · December 24, 2006

Shouldn't work, I wasn't paying attention to what your code should be doing (and that dcounter is the download counter and pcounter is your preview counter, and that neither should increase when this specific script is run)

Anyway, here's what you should have;

[code]<?php
$user="blah";
$host="localhost";
$password="blahblah";
$database="blahblah;
mysql_connect($host,$user,$password);
mysql_select_db($database);
$q = mysql_query("select * from layouts order by id desc");
$n = mysql_num_rows($result);
if (!$n)
{
print "No layouts found.";
} else {
while ($r = mysql_fetch_array($q))
{
print "<table id='box_top'>
<tr>
<td>
{$r['title']}
</td>
</tr>
</table>

<table id='box'>
<tr>
<td valign='top'>
<img src='{$r['previewimage']}' width='160' height='110' border='0' alt=''>
</td>
<td valign='top' width='170'>
Added: {$r['date']} 
Type: {$r['type']} 
Size: {$r['size']} 
Designer: <a href='{$r['artisturl']}' target='_blank'>{$r['artist']}</a> 
Previews: {$r['pcounter']} 
Downloads: {$r['dcounter']} 

<a href='{$r['preview']}'>Preview</a>   <a href='{$r['download']}'>Download</a>
</td>
</tr>
</table>
 ";
}
}
?>[/code]

If you're not going to do any changes on the values retrieved from the database it's useless to store them in a different variable. You can just use the return value instead. However, on preview/download, you should add this code to increase the counter in the table accordingly;

This should be inserted in the download instance, you should pick what to add on your preview instance;
[code]<?php
// code to generate the download instance

$id = 'something'; // here is the reference to where the script is going to get your layout id from
mysql_connect($host,$user,$password);
mysql_select_db($database);
mysql_query("UPDATE layouts SET dcounter=dcounter+1 WHERE id=".$id);

?>[/code]

Hope this is helpful enough.

Kitty · December 24, 2006

You mean I have to save this code:
[code]

<?php
// code to generate the download instance

$id = 'something'; // here is the reference to where the script is going to get your layout id from
mysql_connect($host,$user,$password);
mysql_select_db($database);
mysql_query("UPDATE layouts SET dcounter=dcounter+1 WHERE id=".$id);

?>[/code]

as download.php right?

Also, what should I replace $id = '[b]something[/b]'; with?

Thanks!

kamasheto · December 24, 2006

For example let's say your download.php file looks something like this;

[code]<?php
$id = intval($_GET['id']);
if($id)
{
// in this case my id is already assigned in the top of the page, I can directly use it down the script
$user="blah";
$host="localhost";
$password="blahblah";
$database="blahblah";
mysql_connect($host,$user,$password);
mysql_select_db($database);
mysql_query("UPDATE layouts SET dcounter=dcounter+1 WHERE id=".$id); // notice I used $id, assigned in line 2

// now you can either print the download link or provide a download ticket
// however way you do it, you have your download counter increased by one

} else {
print "something went wrong, you cannot download this file";
}
?>[/code]

Makes any sense?

Kitty · December 24, 2006

Nope! Sorry, I'm not very good at php, I'm just a beginner. :-[

Okay, If I save that code as download.php where should I insert this one?

[code]<?php
// code to generate the download instance

$id = 'something'; // here is the reference to where the script is going to get your layout id from
mysql_connect($host,$user,$password);
mysql_select_db($database);
mysql_query("UPDATE layouts SET dcounter=dcounter+1 WHERE id=".$id);

?>[/code]

Sorry for being so annoying!

kamasheto · December 24, 2006

Nowhere, I already took care of that in the download.php file.

Here's the scenario;

You click here >> domain.com/download.php?id=4

Here's download.php

[code]<?php
$id = intval($_GET['id']);
if($id)
{
$user="blah";
$host="localhost";
$password="blahblah";
$database="blahblah";
mysql_connect($host,$user,$password);
mysql_select_db($database);
mysql_query("UPDATE layouts SET dcounter=dcounter+1 WHERE id=".$id);

// now for example change the header to the file you're downloading, forexample located here >> domain.com/file.zip
header("Location: file.zip");
} else {
print "something went wrong, you cannot download this file";
}
?>[/code]

Kitty · December 24, 2006

Right, I put this code where I want my layouts to display:

[code]<?php
$user="blah";
$host="localhost";
$password="blahblah";
$database="blahblah;
mysql_connect($host,$user,$password);
mysql_select_db($database);
$q = mysql_query("select * from layouts order by id desc");
$n = mysql_num_rows($result);
if (!$n)
{
print "No layouts found.";
} else {
while ($r = mysql_fetch_array($q))
{
print "<table id='box_top'>
<tr>
<td>
{$r['title']}
</td>
</tr>
</table>

<table id='box'>
<tr>
<td valign='top'>
<img src='{$r['previewimage']}' width='160' height='110' border='0' alt=''>
</td>
<td valign='top' width='170'>
Added: {$r['date']} 
Type: {$r['type']} 
Size: {$r['size']} 
Designer: <a href='{$r['artisturl']}' target='_blank'>{$r['artist']}</a> 
Previews: {$r['pcounter']} 
Downloads: {$r['dcounter']} 

<a href='{$r['preview']}'>Preview</a>   <a href='{$r['download']}'>Download</a>
</td>
</tr>
</table>
 ";
}
}
?>[/code]

and I also saved the download.php file, but when I go to my page it says 'No layouts found', though I'm sure I added them to my database.

kamasheto · December 24, 2006

[code]<?php
$user="blah";
$host="localhost";
$password="blahblah";
$database="blahblah"; // there was a " missing here
mysql_connect($host,$user,$password);
mysql_select_db($database);
$q = mysql_query("select * from layouts order by id desc");
$n = mysql_num_rows($q); // we were counting $result, which isn't actually defined
if (!$n)
{
print "No layouts found.";
} else {
while ($r = mysql_fetch_array($q))
{
print "<table id='box_top'>
<tr>
<td>
{$r['title']}
</td>
</tr>
</table>

<table id='box'>
<tr>
<td valign='top'>
<img src='{$r['previewimage']}' width='160' height='110' border='0' alt=''>
</td>
<td valign='top' width='170'>
Added: {$r['date']} 
Type: {$r['type']} 
Size: {$r['size']} 
Designer: <a href='{$r['artisturl']}' target='_blank'>{$r['artist']}</a> 
Previews: {$r['pcounter']} 
Downloads: {$r['dcounter']} 

<a href='{$r['preview']}'>Preview</a>   <a href='{$r['download']}'>Download</a>
</td>
</tr>
</table>
 ";
}
}
?>[/code]

Kitty · December 24, 2006

Thanks, but it still says No layouts found. :(

kamasheto · December 25, 2006

Could you show us your database structure, and you can add or die("something went wrong, error return is: ".mysql_error()); at the end of all the mysql_* functions to know exactly where the problem is.

Kitty · December 25, 2006

Sorry, my mistake, everything works now, but it won't update the database. When I click on the download or preview link it still says 0 downloads/previews.

Kitty · December 27, 2006

Anyone? Please!! :'(

I found this update code somewhere:

[code=php:0]
$db2_query="update `".$prefix_mysql."_layouts` set `downloads`=downloads+'1' where id='$lid'";
$db2_result = mysql_query($db2_query) or die ("Error: $db2_query." . mysql_error());
[/code]

how would I change it? Pretty please!!!

Sign In

PHP and MySQL question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information