MySql - listing?
#1
Posted 24 August 2006 - 08:06 AM
(F.Y.I. If you havent noticed, I don't know anything really about MySql)
#2
Posted 24 August 2006 - 10:17 AM
<?php include ("config.php"); $date = date("d/m/Y"); $ip = $_SERVER['REMOTE_ADDR']; $addcount = mysql_query("INSERT INTO counter(date, ip)"."VALUES ('$date', '$ip')"); $get_unique = mysql_query("SELECT DISTINCT ip FROM counter"); $unique = mysql_num_rows($get_unique); $get_todays_unique = mysql_query("SELECT DISTINCT ip FROM counter WHERE date = '$date'"); $todays_unique = mysql_num_rows($get_todays_unique); echo (" Unique hits: <strong>$unique</strong><br /> Unique hits today: <strong>$todays_unique</strong> "); ?>
config.php should contain your database information, and a way to connect to the database (use the mysql_select_db() and mysql_connect() functions)
Your table should contain two fields, date and ip, like this:
CREATE TABLE `counter` (
`date` varchar(255) NOT NULL default '',
`ip` varchar(255) NOT NULL default ''
)
TYPE=MyISAM;
Hope that helps
#3
Posted 24 August 2006 - 08:52 PM
#4
Posted 25 August 2006 - 05:57 AM
#5
Posted 25 August 2006 - 06:14 AM
#6
Posted 25 August 2006 - 06:31 AM
#7
Posted 25 August 2006 - 03:24 PM
I don't know anything about serialized arrays, but I'm guessing they are just like a normal array, only a bit of code is added to the database so that when you use unserialize() it knows it's an array? Or something like that, I'll look into that process.
But no matter what you would need some way of seperating IP addresses, be it a pipe character (which is most common since it is very rarely used in normal text, |) or if you need can be as simple as a space (' '). You would then use the list/serialize function to split them and insert/extract them into/from an array/database.
As for searching if an IP is in the database, you'd need to use the LIKE search keyword since you won't have only one IP in each row (otherwise, something like a row with '74.52.33.162|74.52.33.163' will not come up with a result if you are looking for just '74.52.33.162').
Something like this should give you a concept of what I am talking about.
<?php $ip = '74.52.33.162'; // Our IP we are looking for, this would probably be $_SERVER['REMOTE_ADDR'] $search_ip = mysql_query("SELECT * FROM `ip_table` WHERE `ip` RLIKE '$ip'") or die(mysql_error()); // Search any rows that contain this IP if(!mysql_num_rows($search_ip)){ // If no results are found // SQL for inserting to the database here } else{ // Do whatever else you was going to do } ?>
RLIKE is a regular expression match basically by the way, synonym for REGEX I believe.
Something like that. If you are wanting to do something like add an IP to a row, you would need a query to select the IP(s) already present in the database and store them into a variable/array, then attach that onto the current IP and make another query to update that row with the new IP row.
I'm not quite sure how your assigning id will work, are you using session ids for that or something like a member's account? Auto-increment alone would make it where there would be a new IP entered no matter what basically.
Edited by Demonslay, 25 August 2006 - 03:25 PM.
#8
Posted 25 August 2006 - 04:35 PM
However they give me a call later, and say it is easy to cheat at this contest as one person can click their own banner hundreds of times and win. So I figured we would put a unique hit counter along with our normal counter.
This is where I need the IP's to be stored in each id. I could just put a general IP logger than does everyones, however, that wouldnt be fair. As if someones IP can count for one banner, it should also count for a different banner from another person.
So here is my explanation of why I need this script. This also explains why what I said earlier probably didnt make sense to you. I figured it was possible to do this, but I might be mistaken. Anyways, let me know if you have any ideas.
Edited by Korndawg, 25 August 2006 - 04:36 PM.
#9
Posted 25 August 2006 - 05:23 PM
muhaha.
ANYWHO, you can either insert the ips like "ip1|1p2|1p3" etc and then explode the string and use aloop to find if the desired ip is within the string, or you can use an array (hint - up) to do it.
#10
Posted 25 August 2006 - 06:04 PM
Now I do.
Korndawg explained this further to me on MSN, then logged out...
He's got a decent little admin control panel built for this thing, lol.
#11
Posted 25 August 2006 - 07:51 PM
Here is my file called in.php, it records the hits_in and im trying to make it record IP's for unique hits.
$db = db_connect(); $link_id = mysql_real_escape_string($_GET['id'],$db); $query = "SELECT hits_in FROM ndlinks_affiliates WHERE link_id='$link_id' LIMIT 1"; $hits = mysql_fetch_array(mysql_query($query, $db)); $hits = $hits['hits_in'] + 1; $query = "UPDATE ndlinks_affiliates SET hits_in='$hits' WHERE link_id='$link_id' LIMIT 1"; mysql_query($query, $db); $ip = $_SERVER['REMOTE_ADDR']; $query = mysql_query("SELECT count(*) FROM ndlinks_affiliates WHERE link_id='$link_id' AND ip = '$ip'"); if (mysql_result($query, 0) == 0) { mysql_query("INSERT INTO ndlinks_affiliates(ip) WHERE link_id='$link_id' VALUES('$ip')"); }
Just so you can get the hint of how it goes...
#12
Posted 26 August 2006 - 10:08 PM
I work for a band that wants to have a contest, to see who can bring the most people to their merch page by clicking a banner given to them. So I got an affiliate manager that counts clicks, create id's, does everything, it works perfectly, I showed the band and they said it'll work. However they give me a call later, and say it is easy to cheat at this contest as one person can click their own banner hundreds of times and win. So I figured we would put a unique hit counter along with our normal counter. This is where I need the IP's to be stored in each id. I could just put a general IP logger than does everyones, however, that wouldnt be fair. As if someones IP can count for one banner, it should also count for a different banner from another person.
I have the script (made by someone else) that im trying to modify. The script is called NDLinks Affiliate Management System, it can be found here. I highly suggest you download it so you can kind of understand what im talking about.
So what I need is in the in.php, where it records the hits, I need it to also record the IP into the same row as the id. Then in the aff.inc I need a snippet that can count all of the IP's in the id's row and give me the #.
Just a little information on me. I suck at PHP and MySql as DemonSlay found out. So I am going to need to see the code you are talking about.
Edited by Korndawg, 26 August 2006 - 10:11 PM.
#13
Posted 26 August 2006 - 10:52 PM
It just really confused me how you had this nice script and all an yet you couldn't understand squat of what I was trying to give you.
Anyways, I gave you some snippets, and stupidly closed that document without saving it at all. >.<
Did you ever get it to finally record the IP? Or do you still have that stupid query completely wrong...
He was trying to use a WHERE clause in an INSERT statement. Then I told him to use an UPDATE statement, and he didn't even use a SET clause, so basically no matter what, nothing happened at all.
Here's some basic syntax maps for ya...
SELECT (columns) FROM (table) WHERE (column) = (value)
INSERT INTO (table) VALUES((value), (value))
UPDATE (table) SET (column)=(value) WHERE (column) = (value)
Of course you can get into all your LIMITs and OFFSETs and tons of other clauses, but that's the bare bones you need.
#14
Posted 27 August 2006 - 03:17 AM
$db = db_connect(); $link_id = mysql_real_escape_string($_GET['id'],$db); $query = "SELECT hits_in FROM ndlinks_affiliates WHERE link_id='$link_id' LIMIT 1"; $hits = mysql_fetch_array(mysql_query($query, $db)); $hits = $hits['hits_in'] + 1; $query = "UPDATE ndlinks_affiliates SET hits_in='$hits' WHERE link_id='$link_id' LIMIT 1"; mysql_query($query, $db); //below is what I need help with $ip = $_SERVER['REMOTE_ADDR']; $query = mysql_query("SELECT count(*) FROM ndlinks_affiliates WHERE link_id='$link_id' AND ip = '$ip'")or die(mysql_error()); if (mysql_result($query, 0) == 0) { mysql_query("UPDATE ndlinks_affiliates SET ip='$ip' WHERE link_id='$link_id' VALUES('$ip')")or die(mysql_error()); }else{ $ips = $query['ip'].$ip; mysql_query("UPDATE ndlinks_affiliates SET ip='$ips' WHERE link_id='$link_id'") or die(mysql_error()); }Heh, after you blocked me (or whatever you did) I realized where the SET should go. The MySql is still screwing up, ill probably end up redownloading the script and starting all over because I think somewhere else in the script I screwed up. Also after I reput all of the "or die(mysql_error());"'s the page it brings up says... "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES('"THE IP"')' at line 1" I change the part where it says THE IP to what my ip was. However there is nothing wrong with their script (which is at line 1) and when I take out the part I modified (from $ip down) it works just fine... any ideas?
Edited by Korndawg, 27 August 2006 - 03:30 AM.
#15
Posted 27 August 2006 - 12:03 PM
Whenever a SQL statement says 'at line 1', it's not at line 1 of the script, but rather line one of the query (I've figured that out since no matter where your query is in your script, it says line 1 and drives me nuts).
And I don't get what you're meaning by 'installing the script'. You shouldn't have to if you are editing the core script of it... installation scripts usually do nothing more than set some variables, maybe 'install' some files and database tables or database files (.db). But even then, you can easily modify the file or the database...
Anyways, you don't use VALUES with an UPDATE query, that's what SET is for. >.<
Take that out and you should be fine. Also put a space before the 'or', as it's kind of squished on there, not sure if that would do anything but just good practice for read-ability.
Also change your if statement to one of these.
if(mysql_result($query, 0) === 0)
if(!mysql_result($query, 0))
if(empty(mysql_result($query, 0)))
if(!mysql_num_rows($query))
if(mysql_num_rows($query) === 0)
Just giving you the different options you have.
I would personally go with the second one.
#16
Posted 27 August 2006 - 07:10 PM
Thanks for clearing up the 'at line 1' issue, I always thought it was the first line of the script. Which in this case is an include.
When I mean installing the script, all I usually mean is changing the mysql stuff (user, pass, host, and db) and uploading it all to my server. That's all I mean when I say "installing the script".
Thanks to you I have this script recording IP's again, Thank You! However it's still only recording one IP at a time, I know it does this because I havent told it to separate IP's with an | or a space. So DemonSlay, or anyone else, what code should I add to this script to get it to record multiple IP's in a single row, by separating them using a | or space?
#17
Posted 27 August 2006 - 08:13 PM
$query = mysql_query("SELECT ip FROM ndlinks_affiliates WHERE link_id='$link_id' AND ip = '$ip'")or die(mysql_error());
Then use this as your else statement, should do that work for ya.
else{ $ips = mysql_result($query, 0).$ip; mysql_query("UPDATE ndlinks_affiliates SET ip='$ips' WHERE link_id='$link_id'") or die(mysql_error()); }
Main thing is that when a result is pulled from the database, I believe it isn't in an array (that's why we have to use mysql_fetch_array()). Not quite sure what it is retrieved as honestly, other than a boolean and some results that can't be used without another function's help.
Edited by Demonslay, 27 August 2006 - 08:14 PM.
#18
Posted 27 August 2006 - 11:01 PM
require 'config.php'; require 'inc/global.php'; $db = db_connect(); $link_id = mysql_real_escape_string($_GET['id'],$db); $query = "SELECT hits_in FROM ndlinks_affiliates WHERE link_id='$link_id' LIMIT 1"; $hits = mysql_fetch_array(mysql_query($query, $db)); $hits = $hits['hits_in'] + 1; $query = "UPDATE ndlinks_affiliates SET hits_in='$hits' WHERE link_id='$link_id' LIMIT 1"; mysql_query($query, $db); $ip = $_SERVER['REMOTE_ADDR']; $query = mysql_query("SELECT ip FROM ndlinks_affiliates WHERE link_id='$link_id' AND ip = '$ip'")or die(mysql_error()); if(!mysql_result($query, 0)) { mysql_query("UPDATE ndlinks_affiliates SET ip='$ip' WHERE link_id='$link_id'") or die(mysql_error()); }else{ $ips = mysql_result($query, 0).$ip; mysql_query("UPDATE ndlinks_affiliates SET ip='$ips' WHERE link_id='$link_id'") or die(mysql_error()); } header("Location: {$conf['site']['url']}Merch/");There is the chance that I misunderstood what you said. I went ahead and put the entire script for this page in here.
However, after inputting this code I tried to go to the site and it gave me these errors...
Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 6 in /home/faktionb/public_html/contest/in.php on line 16
Warning: Cannot modify header information - headers already sent by (output started at /home/faktionb/public_html/contest/in.php:16) in /home/faktionb/public_html/contest/in.php on line 24
Edited by Korndawg, 27 August 2006 - 11:05 PM.
#19
Posted 27 August 2006 - 11:17 PM
if(!mysql_num_rows($query))
That one should work for ya though.
Edited by Demonslay, 27 August 2006 - 11:17 PM.
#20
Posted 27 August 2006 - 11:30 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users