Jump to content


Photo

MySql - listing?


  • Please log in to reply
30 replies to this topic

#1 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 24 August 2006 - 08:06 AM

Ok well im making a unique hits counter and I want it grab the IP of the person, check if the IP is already in the ip section of the id ive specified, if so then to not do anything. However if it is not in the id, I want it to insert it into the IP section of the id ive specified (MySql table). Anyone have any ideas on how to do this? My website is going to have 100's of id's and possibly 1000's of IP's. The script I have right now, just takes the IP and switches it with whats already in my table (ip section). I need to be able to have lots of IP's per id. Does that make any sense to anyone? Because I just confused myself...

(F.Y.I. If you havent noticed, I don't know anything really about MySql)

#2 Indigo

Indigo

    Official Alien

  • Members
  • PipPipPip
  • 617 posts
  • Gender:Male
  • Location:Trondheim, Norway
  • Interests:Computing in general, especially design and programming of all kinds.

Posted 24 August 2006 - 10:17 AM

You could try this:

<?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 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 24 August 2006 - 08:52 PM

It's a nice script. I like it, however it doesnt solve my id problems, and I dont really need the unique hits per day. I just need a snippet/code that will search the ip table, verify if the ip is there, if not then input the ip, if so then not do anything. Really the problem im having is getting my ip table to get more than just one ip per id... For some reason when i use the insert into snippet, it deletes the ip thats there and inputs the new one. Any more ideas?

#4 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 25 August 2006 - 05:57 AM

Sorry to bump this but I am really in need of help... My entire script was supposed to be up and running about two weeks ago.

#5 Matthew.

Matthew.

    Official Spammer .Matt

  • Members
  • PipPipPipPip
  • 2,749 posts
  • Gender:Male
  • Location:England

Posted 25 August 2006 - 06:14 AM

Im not going to make a script for you i cant right now but a couple of things need clearing up. You say you want to see if the IP is in the record you specify (by ID) but how are you separating the ips in the record? By pipe symbol, a serialized array or what?

#6 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 25 August 2006 - 06:31 AM

It's in the MySql db, im new to it but I didnt know I needed to separate it. So as of right now I don't have anything separating them in the db. I don't need a script, I am modifying one I already have. I just need it to record more than one IP in the MySql db.

#7 Demonslay

Demonslay

    P2L Jedi

  • Members
  • PipPipPip
  • 973 posts
  • Gender:Male
  • Location:A strange world where water falls out of the sky... for no reason.
  • Interests:Graphic Design, Coding, Splinter Cell, Cats

Posted 25 August 2006 - 03:24 PM

I'm a little confused as to why you would need multiple IP's per id. Is it a way of checking what IP an account is accessed with, so you can verify the person?
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 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 25 August 2006 - 04:35 PM

Ok maybe I need to explain what I need this for... 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 we give 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.

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 Matthew.

Matthew.

    Official Spammer .Matt

  • Members
  • PipPipPipPip
  • 2,749 posts
  • Gender:Male
  • Location:England

Posted 25 August 2006 - 05:23 PM

Demonslay: http://www.pixel2lif...showtopic=21892

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 Demonslay

Demonslay

    P2L Jedi

  • Members
  • PipPipPip
  • 973 posts
  • Gender:Male
  • Location:A strange world where water falls out of the sky... for no reason.
  • Interests:Graphic Design, Coding, Splinter Cell, Cats

Posted 25 August 2006 - 06:04 PM

Ah, I see now, I remember reading something like that before, I just wasn't 100% sure on how it worked. :P
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 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 25 August 2006 - 07:51 PM

.Matt can you show me how I might be able to do that?

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 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 26 August 2006 - 10:08 PM

Ok well I had another topic but it never really got solved. DemonSlay helped me out for awhile, but he got upset that I don't know hardly anything.

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 Demonslay

Demonslay

    P2L Jedi

  • Members
  • PipPipPip
  • 973 posts
  • Gender:Male
  • Location:A strange world where water falls out of the sky... for no reason.
  • Interests:Graphic Design, Coding, Splinter Cell, Cats

Posted 26 August 2006 - 10:52 PM

Ya, sorry about that, I'm extremely cranky at night after a bike ride. :o
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. :P

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 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 27 August 2006 - 03:17 AM

Ya I apoligize, I am just one of those guys that steals scripts... Someday I plan on being smart enough to write my own but currently I am learning from other peoples scripts. Take a look at this DemonSlay, I got frustrated also, and closed the window without goin back and adding what you told me to...

$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 Demonslay

Demonslay

    P2L Jedi

  • Members
  • PipPipPip
  • 973 posts
  • Gender:Male
  • Location:A strange world where water falls out of the sky... for no reason.
  • Interests:Graphic Design, Coding, Splinter Cell, Cats

Posted 27 August 2006 - 12:03 PM

I didn't block you, I simply logged out 'cuz I knew I'd do nothing but flame you more. :$

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. :P
I would personally go with the second one.

#16 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 27 August 2006 - 07:10 PM

Well I deserve to be flamed, I should have told you right off the bat that I didn't know a thing about this.

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 Demonslay

Demonslay

    P2L Jedi

  • Members
  • PipPipPip
  • 973 posts
  • Gender:Male
  • Location:A strange world where water falls out of the sky... for no reason.
  • Interests:Graphic Design, Coding, Splinter Cell, Cats

Posted 27 August 2006 - 08:13 PM

Change the second query to this.
$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 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 27 August 2006 - 11:01 PM

Ok I did what you said and here is how my code looks now...

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 Demonslay

Demonslay

    P2L Jedi

  • Members
  • PipPipPip
  • 973 posts
  • Gender:Male
  • Location:A strange world where water falls out of the sky... for no reason.
  • Interests:Graphic Design, Coding, Splinter Cell, Cats

Posted 27 August 2006 - 11:17 PM

Try the second to last option then, I think I goofed on the mysql_result() ones, I've never used that function to be honest with you. :S
if(!mysql_num_rows($query))

That one should work for ya though. :D

Edited by Demonslay, 27 August 2006 - 11:17 PM.


#20 Korndawg

Korndawg

    Young Padawan

  • Members
  • Pip
  • 111 posts
  • Gender:Male
  • Location:Texas, USA

Posted 27 August 2006 - 11:30 PM

Ok I didn't know if you wanted me to change the stuff before or not. I didn't and just added this if statement in and it works... It even recorded the ip... However, it recorded my IP so now the db has my IP twice in it, I only need it to record new IP's (to save space). It also didnt put a bar or space between the IP's it just went like 1.2.3.41.2.3.4




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users