Page 1 of 1
problem with mysql and php
Posted: Fri Jan 06, 2006 7:18 am
by louie
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]
Posted: Fri Jan 06, 2006 9:43 am
by Maugrim_The_Reaper
You may want to supply the relevant section of PHP in order for people to figure out whether a PHP error is involved...
Re: php mysql problem
Posted: Sun Jan 08, 2006 6:10 pm
by louie
hawleyjr | Please use Code: 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);
?>
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
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Posted: Mon Jan 09, 2006 12:25 am
by AKA Panama Jack
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.
Posted: Mon Jan 09, 2006 12:53 am
by louie
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....
Posted: Mon Jan 09, 2006 1:04 am
by feyd
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.
Posted: Mon Jan 09, 2006 2:46 am
by mickd
you shouldnt use @ to surpress the errors. get rid of the @ and change
to
Code: Select all
$sql_result=mysql_query($sql) or die(mysql_error());
Posted: Mon Jan 09, 2006 6:03 am
by raghavan20
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...
Posted: Mon Jan 09, 2006 8:54 pm
by louie
<?
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..
Posted: Tue Jan 10, 2006 7:26 am
by raghavan20
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
query = "show databases";
mysql_query(query);
for (i = 0; i < mysql_num_rows(query); i++)
echo mysql_fetch_row[i];
Posted: Tue Jan 10, 2006 8:20 am
by jayshields
I wouldn't recommend logging into your MySQL Server as root, especially into a database with 1.3 million records, imagine someone deleting all them for you!
Posted: Tue Jan 10, 2006 8:27 am
by raghavan20
In addition to what jayshields has said, look
here 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.
Posted: Tue Jan 10, 2006 6:24 pm
by louie
hey. tnx all of you for your replies and sugestions, itt really helped.. I'll go with Panama Jack's opinion... Thank you Panama Jack and to all.. Good day.