Something went awfully wrong in PHP+MySQL :(

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Something went awfully wrong in PHP+MySQL :(
# 8  
Old 11-02-2007
I'm really indebted to you... Your ideas triggered me to actually learn so many new things... Smilie The culprit was in the trace table. When i was querying that table, it was returning many results or should I say duplicate results... Smilie And that was the reason it was slowing down everything... The way I figured this out is that I put a couple of echo statements in my script and observed where the program was slowing down... And that made the trick...!

I have one more doubt: Lets take the structure:
asn1 | ip1 | asn2 | ip2

All are integer fields... I want to actually delete all the duplicate rows in the table.

11 | 12121 | 12 | 23232
34 | 21231 | 32 | 12312
11 | 12121 | 12 | 23232
32 | 23333 | 55 | 22323
11 | 12121 | 12 | 23232

Then I should get something like:
11 | 12121 | 12 | 23232
34 | 21231 | 32 | 12312
32 | 23333 | 55 | 22323

into another table or anyway its possible. I think DISTINCT can do this but don't know if its possible with my table. I'm currently doing it as

Any suggestions?
# 9  
Old 11-02-2007
I'm really indebted to you...
So am I to many Smilie No problem about that

To delete duplicate rows from the table, you could try something like,

delete from table1 where rowid not in ( select min(rowid) from table1 group by column1 );

what about the multiple insert statements and a commit following that ?
Did that improve performance ?
# 10  
Old 11-02-2007
Actually, I still didn't specifically commit anything... I just used a single insert statement in a loop. Other than that I haven't done anything special and yeah I improved the queries too... And regarding your suggestion, what do I do when there's no rowid in my table?

Last edited by Legend986; 11-02-2007 at 02:55 AM..
# 11  
Old 11-02-2007
Actually, I still specifically commit anything... I just used a single insert statement in a loop.
Am sorry, I don't understand your statement.

Are you using
explicit commit

begin transaction

or implicit commit

insert ( auto commit )
insert ( auto commit )
insert ( auto commit )

And regarding your suggestion, what do I do when there's no rowid in my table?

I suppose you are using Oracle DB, rowid is not a column its a pseudo column which uniquely identifies a record in a table
# 12  
Old 11-02-2007
Sorry that was a grammar typo... Corrected now... I'm doing it in the following way:

for($i=2;$i<2749750;$i++) {
	if($i == 2) {
		//This is the first row. So fetch it as usual and break out of the loop
		$sql_prev = "SELECT ip,gid FROM trace WHERE ID=".($i-1);
		$result_prev = mysql_query($sql_prev) or die(mysql_error());
		$row_prev = mysql_fetch_assoc($result_prev);
	//If $i is not 2 then we fetched the previous row. So lets compare now
	$sql_cur = "SELECT ip,gid FROM trace WHERE ID=".$i;
	$result_cur = mysql_query($sql_cur);
	$row_cur = mysql_fetch_assoc($result_cur);
		$sql_prev_asn = "SELECT asn FROM asn_number WHERE ip_address=$row_prev[ip] LIMIT 1";
		$result_prev_asn = mysql_query($sql_prev_asn);
		$row_prev_asn = mysql_fetch_assoc($result_prev_asn);
		$sql_cur_asn = "SELECT asn FROM asn_number WHERE ip_address=$row_cur[ip] LIMIT 1";
		$result_cur_asn = mysql_query($sql_cur_asn);
		$row_cur_asn = mysql_fetch_assoc($result_cur_asn);
		if(($row_prev_asn['asn'] != $row_cur_asn['asn']) && ($row_prev['gid'] == $row_cur['gid'])) {
	                $sql = "INSERT INTO br(asn1,ip1,asn2,ip2) VALUES('$row_prev_asn[asn]','$row_prev[ip]','$row_cur_asn[asn]','$row_cur[ip]')";
	                $result = mysql_query($sql) or die(mysql_error());
	$row_prev = $row_cur;

So, I'm not using any explicit commit statements... I'm writing this code in PHP. And well, I'm not using Oracle. I'm using MySQL...
# 13  
Old 11-02-2007
Oops! I didn't check the title

that you are using MySQL

Will check the equivalent of rowid in MySql and update !

Sorry for the confusion !


That's pretty much you are using commit for every transaction which will drastically bring down the performance.

Combine the 'n' number of inserts in a transaction and the commit a single transaction alone, that would speed up.

Again, this depends upon the criticality of the application you are working, if the application is really critical keep the value of 'n' really low.
# 14  
Old 11-02-2007
Do you have any idea on how to get the connected components in the database? I mean, referring to the old table...i.e.

asn1 ip1 asn2 ip2

I want to get all the connected components with reference to ip1 and ip2. For example, consider the following:

asn1 ip1 asn2 ip2
1 | 121 | 2 | 12121
1 | 123 | 2 | 121
1 | 643 | 2 | 344

I should get something like:
121, 12121, 123
643, 344

Is that possible?
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Web Development

Can't Install MySQL with PHP

Hi, I'm on a Raspberry Pi with Raspbian Wheezy. I urgently need to get MySQL running with PHP, but I get an error. For example: $con=mysql_connect("","root","******","ids"); gives PHP Fatal error: Call to undefined function mysql_connect() So, I found I needed to install some... (2 Replies)
Discussion started by: FreddoT
2 Replies

2. Programming


Hello, While I was interpretation the PHP manual on database security the recent past, it said that you should by no means connect to the database as the super user but rather as one more user with more limited options. My question is: How do you generate new users and set access... (2 Replies)
Discussion started by: AimyThomas
2 Replies

3. Emergency UNIX and Linux Support

Migration of website... PHP/Mysql -which path for DB.php

Hi, I have two websites: and I didn't write either but have successfully moved all the files from to I (thought) I installed all the correct php modules and website2 is mostly up and running. However, my boss found that when we go to a... (15 Replies)
Discussion started by: Astrocloud
15 Replies

4. Shell Programming and Scripting

Mysql is not connected in php

Hi, The php is not able to connect into my mysql database. But i can able to connect by manually. I think that I have missed some points. Please guild for the same. Thanks, Mani (1 Reply)
Discussion started by: Mani_apr08
1 Replies

5. Programming


Hello every one i have question i want to build DATAbase using PHP as interface i use shell to access to linux . i have in linux psql and SQLplus i'll call all html files that has db tabels from shell directory. what should to do before design php pages. can build the database sql design... (3 Replies)
Discussion started by: Scotch
3 Replies

6. Shell Programming and Scripting

PHP/MySQL slow_queries

Hi All, I have a problem with my database having lots of 'stale' slow_queries. I think the problem may be because of the following code: $numresults=mysql_query("select * from links where catagory=".$catagory." order by linknum"); $numrows=mysql_num_rows($numresults); I believe this... (4 Replies)
Discussion started by: pondlife
4 Replies

7. Shell Programming and Scripting

Problem with PHP and MySQL

Okay, I'm new to this PHP and MySQL stuff, so help would be VERY much appreciated. :) On my iMac runnning Panther, it has MySQL and PHP installed. Yet when I view a PHP file from the iMac or another computer at my house, I get the source code. What's wrong? (11 Replies)
Discussion started by: Danny_10
11 Replies

8. UNIX for Dummies Questions & Answers


I want to design a database, using mysql as a backend, and PHP as the frontend, I wanna be able to easily build forms in PHP to communicate with MySQL, is there any programs that will allow this, I really dont want to program all the forms by hand.. thankyou (2 Replies)
Discussion started by: kwalick
2 Replies

9. Cybersecurity

mysql php

with a limited knowledege of php and sql, what is a good and secure way to do passwords running an https server? (1 Reply)
Discussion started by: macdonto
1 Replies
Login or Register to Ask a Question