mySQL problem

programming
1

ok i am setting up a db so that when someone clicks a link it passes through another page and updates the db. get it? good. but when i am settin up the db, i just realized that when i show it in a table, the name doesnt work! here is the code:


<?php
$linkID = mysql_connect("localhost", "mdipi_links", "mikeyd");
mysql_select_db("mdipi_links",$linkID);
$result = mysql_query("INSERT INTO hitsT (hitID, hits) VALUES ('vone', 2)", $linkID);
if ($result == true){ 
print "One record has been added successfully.<p>";
}
else{
print "Records could not be added.<p>";
}
$resultID = mysql_query("SELECT * FROM hitsT", $linkID);
print "<table boarder=\"1\"><tr><th>Link Name</th>";
print "<th>Hits</th>";
while ($row = mysql_fetch_row($resultID))
{
print "<tr>";
foreach ($row as $field)
{
print "<td>$field</td>";
}
print "</tr>";
}
print "</table>";

mysql_close($linkID);
?>

this is where the name is for the links…but it just doesnt work…

$result = mysql_query("INSERT INTO hitsT (hitID, hits) VALUES ('vone', 2)", $linkID);

this is the site that i view it at…http://www.mdipi.com/sorry/sqltest.php

if anyone can help me with getting this name to work it would be great!

2

All right Dipster… Because of the power outages my work was cancelled so I’m here to teach you how to do this…

You have the right idea, but you’re off just a bit…

make a file. Name it dlFile.php Now in this file you are going to want to do the following things:

  1. Connect to your database.

  2. Get the current count for the link pressed.

  3. Add 1 to the count, and update the table with the new data

  4. Redirect to the require page.

We’ll come back to that later. Now what you want to do is set up the database like this:

3 fields: path, hitID, and counter

path is going to be the link that you want to redirect to. Say, “http://www.kirupa.com”

hitID is going to be a unique identification number for that link. The best way to do this is so that it auto-increments (i’ll show you later) each time a new row is added to the table.

counter is pretty self-explanitory right?

Ok, say we start to fill up our database, so we have 2 entries. say it looks like this:

we’re going to use these as examples.

Now on your main page, you are going to have to set things up a bit differently than you normally would. Because you aren’t going to be sending them directly to their desination, you want them to stop off at dlFile.php first. So your links are going to look like this:

<a href="dlFile.php?hitID=001">Kirupa.com</a>

and you are going to be sending the hitID thru the URL string so the code in dlFile.php can grab it.

now lets get to the code in dlFile.php First we want to connect to the database:


<?
$hitID = $_GET['hitID']; //just grabs the variable

$linkID = mysql_connect("localhost", "userNAME", "passWORD");

mysql_select_db("dbNAME", $linkID);

That was easy enough, you know how to do that. Now lets do the query command to get the current counter value.


// Defines the query to run
$query = "SELECT * FROM hitsT WHERE hitID='$hitID'";
// Executes the query
$resultID = mysql_query($query, $linkID);
// Grab the data as an associative array
$row = mysql_fetch_assoc($resultID);

$count = $row['counter'] + 1; //defines out counter value

There are other ways to grab data, but I like using associative array for some reason. Its just what I use, other ways may work better for you.

Now we want to update the code



// Issue a new query updating the code...
$query2 = "UPDATE hitsT SET counter='$count' WHERE hitID='$hitID'";
//Execute the new query
$resultID2 = mysql_query($query2, $linkID);

That should have updated the code in the table, but we’re not done… we need to send the user on their way to their destination


// We can still use our array from our first query because we haven't
// overwritten it yet

// Close the DB since we are done with it
mysql_close($l);

// Send them to the page they want with header()
header("Location: ".$row['path']);
?>

Thats it for getting the info from the DB. Just in case my power goes out I’m going to save this post and then post again about how to make your database auto increment and I’ll show you how to enter all your links so you don’t have to do it by hand.

3

I suggest getting phpMyAdmin if you are going to be dealing with database stuff… but if not then here is the code to create the table you want:


CREATE TABLE `hitsT` (`path` VARCHAR( 255 ) NOT NULL , `hitID` INT( 3 ) NOT NULL AUTO_INCREMENT , `counter` INT( 4 ) NOT NULL , PRIMARY KEY ( `hitID` ) );

you would issue that like any other query. and then result it to make sure it ran correctly. That takes care of the auto increment problem.

4

Just a quick note: if you’re serious about using a database, take the time to learn SQL. It’s easy to learn and very powerful. I would recommend SAMS Teach Yourself mySQL in 24 Hours, which was an easy read and had some nice practice examples with PHP in the back.

5

Don’t listen to him dipi! :wink: Get the SQL book for the same series as the PHP book that you have now. :slight_smile:

Yates: Thats a good book too! :thumb:

6

yates i got that book only its for PHP :thumb:

jubba i will :thumb:

see i got jubbas book after i thought i lost the teach yourself PHP in 24hrs. then i found it under two or three phone books in a drawr…grr. anyway i will pick one of the two up cuase jubbas book is fast, but the serise yeates is talkin about is big in detail for n00bs. choices choices…