problem with mysql and php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
louie
Forum Newbie
Posts: 5
Joined: Fri Jan 06, 2006 7:06 am

problem with mysql and php

Post 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]
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post 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...
louie
Forum Newbie
Posts: 5
Joined: Fri Jan 06, 2006 7:06 am

Re: php mysql problem

Post by louie »

hawleyjr | Please use

Code: Select all

and

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

and

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]
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
louie
Forum Newbie
Posts: 5
Joined: Fri Jan 06, 2006 7:06 am

Post 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....
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
mickd
Forum Contributor
Posts: 397
Joined: Tue Jun 21, 2005 9:05 am
Location: Australia

Post by mickd »

you shouldnt use @ to surpress the errors. get rid of the @ and change

Code: Select all

$sql_result=@mysql_query($sql);
to

Code: Select all

$sql_result=mysql_query($sql) or die(mysql_error());
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
louie
Forum Newbie
Posts: 5
Joined: Fri Jan 06, 2006 7:06 am

Post 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..
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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

Code: Select all

echo $dbc;
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];
Last edited by raghavan20 on Tue Jan 10, 2006 8:28 am, edited 1 time in total.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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!
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

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.
louie
Forum Newbie
Posts: 5
Joined: Fri Jan 06, 2006 7:06 am

Post 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.
Post Reply