problem with mysql and php
Moderator: General Moderators
problem with mysql and php
Can anyone help me?? I quite new to php and mysql and I have this problem.. I have a datavase containing a table and 1.3 million records... whenever i execute a simple query (i.e.. fetch a row of data), there aren't any result to the query even though it can be seen when you use mysql interface.. i consulted my supervisor and told me that its a php code error... I've through a lot of research but it seems that there aren/t any solution to this problem... please help... anyone...[/b]
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
Re: php mysql problem
hawleyjr | Please use
What it does is to fetch a certain data in the database.. I made it to test the database.
Its a very simple query, but it doesn't do what it is supposed to do.. I think it may be a hardware problem because mysql and apache uses a lot of resource my pc can't cope with the required resource alotment that is why I can't get it to work.. Am I right?? I am using a Pentium 3 pc (800Mhz) with 256 sdram, Apache 2, PHP 4.3.1 and MYSQL 4.1.. By the way thanks for the immediate reply..
hawleyjr | Please use
Code: Select all
andCode: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Oh yes.. well I coded a simple script like this:Code: Select all
<?
include("includes/connect.php");
$sql="select clientname from courtcase";
$sql_result=@mysql_query($sql);
$row=@mysql_fetch_array($sql_result);
echo $row['clientname']."<br>";
mysql_free_result($sql_result);
?>Its a very simple query, but it doesn't do what it is supposed to do.. I think it may be a hardware problem because mysql and apache uses a lot of resource my pc can't cope with the required resource alotment that is why I can't get it to work.. Am I right?? I am using a Pentium 3 pc (800Mhz) with 256 sdram, Apache 2, PHP 4.3.1 and MYSQL 4.1.. By the way thanks for the immediate reply..
hawleyjr | Please use
Code: Select all
andCode: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
That query is going to fetch all 1.3 million client names and you may be running out of memory for that php execution.
Why in the world are you executing a query like that on a 1.3 million record table? That is very memory and CPU intensive as it will scan the entire table to build the record set and the record set will be huge.
Especially since you are apparently only using ONE record. You need a where statement in there to limit the record set and if you only want one record you need to add a LIMIT to the SQL query.
What you posted is very, very inefficient.
Why in the world are you executing a query like that on a 1.3 million record table? That is very memory and CPU intensive as it will scan the entire table to build the record set and the record set will be huge.
Especially since you are apparently only using ONE record. You need a where statement in there to limit the record set and if you only want one record you need to add a LIMIT to the SQL query.
What you posted is very, very inefficient.
I'm sorry for that, I forgot to include my other queries.. The one I posted was my later query. The original one contained a where clause and limit clause but it still had the same result.. So it is very resource intensive.. ok.. tnx for the info so ur suggesting that we use a pc that has more resource than what we are using right now... Am I right?? tnx very much for the immediate reply....
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
When record amounts get that high, you should be using a (possibly) dedicated, high power, server as the database server in many instances. However, if the table you are querying against isn't properly optimized, you are severly degrading the performance of the query. Have a look at what an EXPLAIN of that query reports.
you shouldnt use @ to surpress the errors. get rid of the @ and change
to
Code: Select all
$sql_result=@mysql_query($sql);Code: Select all
$sql_result=mysql_query($sql) or die(mysql_error());- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
Please show the code in include.php as well.
I see all you table names and field names are in lower case. Make sure they are in proper case. I see mostly Mysql in Linux properly uses naming syntax...
for ex...MemberOrderMapping_tbl will sometimes be represented as memberordermapping_tbl in Mysql running under Windows. I can say for sure database names are represented like the above example.
Please use backticks for queries.
Try to run a sample query with where clause in a Mysql client and find out whether you get the result for that...
I see all you table names and field names are in lower case. Make sure they are in proper case. I see mostly Mysql in Linux properly uses naming syntax...
for ex...MemberOrderMapping_tbl will sometimes be represented as memberordermapping_tbl in Mysql running under Windows. I can say for sure database names are represented like the above example.
Please use backticks for queries.
Try to run a sample query with where clause in a Mysql client and find out whether you get the result for that...
<?
define ('DB_USER', 'root'); // Database User Name
define ('DB_PASSWORD', ''); // Database User Password
define ('DB_HOST', 'localhost'); // Host Name (mostly localhost)
$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD); // Establishes connection
mysql_select_db('cidb',$dbc);
?>
this is the script i used to connect to the database... I tried my script using the mysql client, it worked but took a long time to produce the result. I also removed the @ sign before the mysql_query function but it still doesn't work.. is there any other idea?? tnx for the reply everyone, it helped me greatly..
define ('DB_USER', 'root'); // Database User Name
define ('DB_PASSWORD', ''); // Database User Password
define ('DB_HOST', 'localhost'); // Host Name (mostly localhost)
$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD); // Establishes connection
mysql_select_db('cidb',$dbc);
?>
this is the script i used to connect to the database... I tried my script using the mysql client, it worked but took a long time to produce the result. I also removed the @ sign before the mysql_query function but it still doesn't work.. is there any other idea?? tnx for the reply everyone, it helped me greatly..
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
I do not think mysql would let you use the root account without a password. Try something like this to check whether you have connection with mysql
By the way make sure the database name is correct as well....
if you want to check on a query that yields a few number of records ,,,you can something like
Code: Select all
echo $dbc;if you want to check on a query that yields a few number of records ,,,you can something like
Code: Select all
query = "show databases";
mysql_query(query);
for (i = 0; i < mysql_num_rows(query); i++)
echo mysql_fetch_row[i];
Last edited by raghavan20 on Tue Jan 10, 2006 8:28 am, edited 1 time in total.
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
In addition to what jayshields has said, lookhere for how to create a user.
In most real time environments, nobody would connect to mysql as root instead you have to create users for localhost (who connect from your local system) or % (who can connect from anywhere by identifying your mysql server with a host name or IP address). After you have created user, you have to grant him/her permissions.
In most real time environments, nobody would connect to mysql as root instead you have to create users for localhost (who connect from your local system) or % (who can connect from anywhere by identifying your mysql server with a host name or IP address). After you have created user, you have to grant him/her permissions.