php-general Digest 11 Apr 2010 10:47:01 -0000 Issue 6686
Topics (messages 303973 through 303985):
Re: Inserting into multiple tables
303973 by: Adam Richardson
303974 by: Gary
303980 by: Adam Richardson
303981 by: Gary
303983 by: Tommy Pham
PHP execute very slow : PHP Version 5.2.6
303975 by: Kristijan Marin
303976 by: Kristijan Marin
303984 by: kranthi
Determining Top # from MySQL
303977 by: Ashley M. Kirchner
303978 by: Kevin Kinsey
303979 by: Dan Joseph
303982 by: vikash.iitb.gmail.com
303985 by: Floyd Resler
Administrivia:
To subscribe to the digest, e-mail:
[email protected]
To unsubscribe from the digest, e-mail:
[email protected]
To post to the list, e-mail:
[email protected]
----------------------------------------------------------------------
--- Begin Message ---
On Sat, Apr 10, 2010 at 5:27 PM, Gary <[email protected]> wrote:
> I am experimenting with multiple tables, it is only a test that is my local
> machine only. This is the current code, which does not work , I have tried
> to concatonate the insert statements. I have tried multiple $query
> variables, but it is just overwriting itself (only the last one gets
> inserted). I also tried writing the $query as an array, which got me an
> error message (saying it was expecting a string and I offered an array).
>
> Someone point me in the right direction?
>
>
I'm not aware of any multi-table insert syntax:
http://lists.mysql.com/mysql/171921
You'd have to perform 4 separate mysql_query() calls to accomplish what
you're looking for (one for each insert.)
That said, I'd recommend using a transaction so you make sure all of the
inserts succeed or fail together (it would cause issues down the road if one
table insert worked and another failed.) Additionally, I'd recommend using
prepared statements to avoid SQL injection (I'd mention validation your POST
data, too ;)
See the tutorial below:
http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html
Adam
--
Nephtali: PHP web framework that functions beautifully
http://nephtaliproject.com
--- End Message ---
--- Begin Message ---
Adam
Thanks for your reply, but I think I am totally confused. Not so much by
your answer (although I admit I did not get it to work yet), but by the
entire multiple table issue. Several books I use talk about the importance
of using multiple tables, yet are thin on the insert issue. Searching the
web I have read several answers saying you simply cant insert into multiple
tables at once.
So if multiple tables is such an important issue, why is there no insert
solution. What am I missing?
Gary
"Adam Richardson" <[email protected]> wrote in message
news:[email protected]...
On Sat, Apr 10, 2010 at 5:27 PM, Gary <[email protected]> wrote:
I am experimenting with multiple tables, it is only a test that is my
local
machine only. This is the current code, which does not work , I have
tried
to concatonate the insert statements. I have tried multiple $query
variables, but it is just overwriting itself (only the last one gets
inserted). I also tried writing the $query as an array, which got me an
error message (saying it was expecting a string and I offered an array).
Someone point me in the right direction?
I'm not aware of any multi-table insert syntax:
http://lists.mysql.com/mysql/171921
You'd have to perform 4 separate mysql_query() calls to accomplish what
you're looking for (one for each insert.)
That said, I'd recommend using a transaction so you make sure all of the
inserts succeed or fail together (it would cause issues down the road if
one
table insert worked and another failed.) Additionally, I'd recommend
using
prepared statements to avoid SQL injection (I'd mention validation your
POST
data, too ;)
See the tutorial below:
http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html
Adam
--
Nephtali: PHP web framework that functions beautifully
http://nephtaliproject.com
__________ Information from ESET NOD32 Antivirus, version of virus
signature database 5016 (20100410) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 5016 (20100410) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
--- End Message ---
--- Begin Message ---
On Sat, Apr 10, 2010 at 7:56 PM, Gary <[email protected]> wrote:
> Adam
>
> Thanks for your reply, but I think I am totally confused. Not so much by
> your answer (although I admit I did not get it to work yet), but by the
> entire multiple table issue. Several books I use talk about the importance
> of using multiple tables, yet are thin on the insert issue. Searching the
> web I have read several answers saying you simply cant insert into multiple
> tables at once.
>
> So if multiple tables is such an important issue, why is there no insert
> solution. What am I missing?
>
> Gary
>
>
> "Adam Richardson" <[email protected]> wrote in message
> news:[email protected]...
>
>> On Sat, Apr 10, 2010 at 5:27 PM, Gary <[email protected]> wrote:
>>
>> I am experimenting with multiple tables, it is only a test that is my
>>> local
>>> machine only. This is the current code, which does not work , I have
>>> tried
>>> to concatonate the insert statements. I have tried multiple $query
>>> variables, but it is just overwriting itself (only the last one gets
>>> inserted). I also tried writing the $query as an array, which got me an
>>> error message (saying it was expecting a string and I offered an array).
>>>
>>> Someone point me in the right direction?
>>>
>>>
>>> I'm not aware of any multi-table insert syntax:
>> http://lists.mysql.com/mysql/171921
>>
>> You'd have to perform 4 separate mysql_query() calls to accomplish what
>> you're looking for (one for each insert.)
>>
>> That said, I'd recommend using a transaction so you make sure all of the
>> inserts succeed or fail together (it would cause issues down the road if
>> one
>> table insert worked and another failed.) Additionally, I'd recommend
>> using
>> prepared statements to avoid SQL injection (I'd mention validation your
>> POST
>> data, too ;)
>>
>> See the tutorial below:
>> http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html
>>
>> Adam
>>
>> --
>> Nephtali: PHP web framework that functions beautifully
>> http://nephtaliproject.com
>>
>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus
>> signature database 5016 (20100410) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 5016 (20100410) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
>
> http://www.eset.com
>
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
Hi Gary,
The benefit of breaking up the data across multiple tables (when the data
has certain characteristics) is that you can better protect the integrity of
the data through what's called normalization (
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html.)
If you have a database in which you'd like to store employees, perhaps one
of the fields would be the address of the office building they work in.
Now, you could build the database so it uses one table to store all of the
data (first name, last name, office building address, etc.) Up front, this
might seem reasonable, but what happens when employees in office building A
move to a new location? In this table, you'd have to carefully replace the
address for each employee with the new address.
How will you find all of the employees that need to have the address
updated? Did you type in the address exactly the same (down to even the
spaces in the words) in every row? It's very possible that this scheme will
eventually lead to errors.
However, the other approach would be to break this database up into 2
tables, one for the core employee data (first name, last name, etc.), and
one table for office building locations because there is redundancy. Now
the employee table wouldn't actually contain the actual office building
address, it would contain a reference to the row in the office building
address table. When you want to change the address for an office building,
you merely change one row and it impacts every employee avoiding the
potential pitfalls of the first example using 1 table for everything.
That said, just because this scheme better protects the integrity of the
data doesn't mean it's easier. Sometimes your inserts will have to impact
multiple tables, and they will all have to be performed separately.
However, after inserting OR updating your data, you will be able to sleep
at night knowing that your data properly reflects the intentions of the
changes you've made.
Now, looking at your example, I notice very little redundancy. That is,
unless someone can submit comments again later on, all of the fields would
be unique to the particular individual submitting the form. This data could
easily be stored in the same table (just include an id field as a primary
key), and there shouldn't be any issues.
I'd recommend reading a book like that below that works through PHP AND
MySQL in a very nice manner:
http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/ref=sr_1_1?ie=UTF8&s=books&qid=1270953434&sr=8-1
I probably won't provide much follow-up on MySQL database normalization in
follow-up emails so-as not to upset others on the list as this list focuses
on PHP, but I hope I helped you get started on the right track and avoid
becoming discouraged. It takes some time to get it to "click", but once it
does, DB development will be just as fun and easy as PHP ;)
Adam
--
Nephtali: PHP web framework that functions beautifully
http://nephtaliproject.com
--- End Message ---
--- Begin Message ---
Adam
Thank you for your well thought out response.
Two points:
I did not include any anti-injection functions because this was an
experiment for multiple tables, it is on my machine only.
Since these are php scripts, I dont think anyone will mind (not to mention
this board always provides great answers).,
However I think I may have answered my question about the importance of
normalization of tables. I have written a number of databases used on
various web sites, however, they all are used as a collection of data from
input forms.
Most of the information about mutilple tables deals with the retreval of
data from, not inserting into, meaning they are more used for known data
inserted by the database owner/administrator to be retrieved by queries into
the DB.
Am I on the right track?
Again, thank you for your replies.
Gary
"Adam Richardson" <[email protected]> wrote in message
news:[email protected]...
> On Sat, Apr 10, 2010 at 7:56 PM, Gary <[email protected]> wrote:
>
>> Adam
>>
>> Thanks for your reply, but I think I am totally confused. Not so much by
>> your answer (although I admit I did not get it to work yet), but by the
>> entire multiple table issue. Several books I use talk about the
>> importance
>> of using multiple tables, yet are thin on the insert issue. Searching
>> the
>> web I have read several answers saying you simply cant insert into
>> multiple
>> tables at once.
>>
>> So if multiple tables is such an important issue, why is there no insert
>> solution. What am I missing?
>>
>> Gary
>>
>>
>> "Adam Richardson" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>> On Sat, Apr 10, 2010 at 5:27 PM, Gary <[email protected]> wrote:
>>>
>>> I am experimenting with multiple tables, it is only a test that is my
>>>> local
>>>> machine only. This is the current code, which does not work , I have
>>>> tried
>>>> to concatonate the insert statements. I have tried multiple $query
>>>> variables, but it is just overwriting itself (only the last one gets
>>>> inserted). I also tried writing the $query as an array, which got me an
>>>> error message (saying it was expecting a string and I offered an
>>>> array).
>>>>
>>>> Someone point me in the right direction?
>>>>
>>>>
>>>> I'm not aware of any multi-table insert syntax:
>>> http://lists.mysql.com/mysql/171921
>>>
>>> You'd have to perform 4 separate mysql_query() calls to accomplish what
>>> you're looking for (one for each insert.)
>>>
>>> That said, I'd recommend using a transaction so you make sure all of the
>>> inserts succeed or fail together (it would cause issues down the road if
>>> one
>>> table insert worked and another failed.) Additionally, I'd recommend
>>> using
>>> prepared statements to avoid SQL injection (I'd mention validation your
>>> POST
>>> data, too ;)
>>>
>>> See the tutorial below:
>>> http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html
>>>
>>> Adam
>>>
>>> --
>>> Nephtali: PHP web framework that functions beautifully
>>> http://nephtaliproject.com
>>>
>>>
>>>
>>> __________ Information from ESET NOD32 Antivirus, version of virus
>>> signature database 5016 (20100410) __________
>>>
>>> The message was checked by ESET NOD32 Antivirus.
>>>
>>> http://www.eset.com
>>>
>>>
>>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus
>> signature database 5016 (20100410) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>>
>> http://www.eset.com
>>
>>
>>
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
> Hi Gary,
>
> The benefit of breaking up the data across multiple tables (when the data
> has certain characteristics) is that you can better protect the integrity
> of
> the data through what's called normalization (
> http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html.)
>
> If you have a database in which you'd like to store employees, perhaps one
> of the fields would be the address of the office building they work in.
> Now, you could build the database so it uses one table to store all of the
> data (first name, last name, office building address, etc.) Up front,
> this
> might seem reasonable, but what happens when employees in office building
> A
> move to a new location? In this table, you'd have to carefully replace
> the
> address for each employee with the new address.
>
> How will you find all of the employees that need to have the address
> updated? Did you type in the address exactly the same (down to even the
> spaces in the words) in every row? It's very possible that this scheme
> will
> eventually lead to errors.
>
> However, the other approach would be to break this database up into 2
> tables, one for the core employee data (first name, last name, etc.), and
> one table for office building locations because there is redundancy. Now
> the employee table wouldn't actually contain the actual office building
> address, it would contain a reference to the row in the office building
> address table. When you want to change the address for an office
> building,
> you merely change one row and it impacts every employee avoiding the
> potential pitfalls of the first example using 1 table for everything.
>
> That said, just because this scheme better protects the integrity of the
> data doesn't mean it's easier. Sometimes your inserts will have to impact
> multiple tables, and they will all have to be performed separately.
> However, after inserting OR updating your data, you will be able to sleep
> at night knowing that your data properly reflects the intentions of the
> changes you've made.
>
> Now, looking at your example, I notice very little redundancy. That is,
> unless someone can submit comments again later on, all of the fields would
> be unique to the particular individual submitting the form. This data
> could
> easily be stored in the same table (just include an id field as a primary
> key), and there shouldn't be any issues.
>
> I'd recommend reading a book like that below that works through PHP AND
> MySQL in a very nice manner:
> http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/ref=sr_1_1?ie=UTF8&s=books&qid=1270953434&sr=8-1
>
> I probably won't provide much follow-up on MySQL database normalization in
> follow-up emails so-as not to upset others on the list as this list
> focuses
> on PHP, but I hope I helped you get started on the right track and avoid
> becoming discouraged. It takes some time to get it to "click", but once
> it
> does, DB development will be just as fun and easy as PHP ;)
>
> Adam
>
> --
> Nephtali: PHP web framework that functions beautifully
> http://nephtaliproject.com
>
>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5016 (20100410) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
__________ Information from ESET Smart Security, version of virus signature
database 5016 (20100410) __________
The message was checked by ESET Smart Security.
http://www.eset.com
--- End Message ---
--- Begin Message ---
Hi Gary,
> -----Original Message-----
> From: Gary [mailto:[email protected]]
> Sent: Saturday, April 10, 2010 2:28 PM
> To: [email protected]
> Subject: [PHP] Inserting into multiple tables
>
> I am experimenting with multiple tables, it is only a test that is my
> local
> machine only. This is the current code, which does not work , I have
> tried
> to concatonate the insert statements. I have tried multiple $query
> variables, but it is just overwriting itself (only the last one gets
> inserted). I also tried writing the $query as an array, which got me an
> error message (saying it was expecting a string and I offered an
> array).
>
> Someone point me in the right direction?
>
> Gary
>
> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
> <html xmlns="http://www.w3.org/1999/xhtml";>
> <head>
> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
> <title>Untitled Document</title>
> </head>
>
> <body>
>
> <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
>
> <label>First Name </label> <input name="fname" type="text" /><br /><br
> />
> <label>Last Name </label><input name="lname" type="text" /><br /><br />
> <label>Street Address </label><input name="street" type="text" /><br
> /><br
> />
> <label>Town </label><input name="town" type="text" /><br /><br />
> <label>State </label><input name="state" type="text" /><br /><br />
> <label>Zip Code</label><input name="zip" type="text" /><br /><br />
> <label>Telephone</label><input name="phone" type="text" /><br /><br />
> <label>Fax</label><input name="fax" type="text" /><br /><br />
> <label>E-Mail</label><input name="email" type="text" /><br /><br />
> <label>Comments</label><br /><textarea name="comments" cols="100"
> rows="15"></textarea><br /><br />
>
> <input name="submit" type="submit" value="submit" />
> </form>
>
> <?php
>
> $fname=($_POST['fname']);
> $lname=($_POST['lname']);
> $street=($_POST['street']);
> $town=($_POST['town']);
> $state=($_POST['state']);
> $zip=($_POST['zip']);
> $phone=($_POST['phone']);
> $fax=($_POST['fax']);
> $email=($_POST['email']);
> $comments=($_POST['comments']);
> $REMOTE_ADDR=$_SERVER['REMOTE_ADDR'];
>
> $dbc=mysqli_connect('localhost','root','','test');
> $query="INSERT INTO address (street, town, state,
> zip)"."VALUES('$street','$town','$state','$zip')".
> "INSERT INTO comments(comments)"."VALUES('$comments')".
> "INSERT INTO
> contact(phone,fax,email)"."VALUES('$phone','$fax','$email')".
> "INSERT INTO name (fname, lname)"."VALUES('$fname','$lname')";
>
> $result = mysqli_query($dbc, $query)
> or die('Error querying database.');
>
I see 2 problems:
1) your sql statements are not separated by semicolon <- very important when
executing multiquery
2) you could try mysql_multi_query
http://www.php.net/manual/en/mysqli.multi-query.php
Regards,
Tommy
> mysqli_close($dbc);
>
> ?>
> </body>
> </html>
>
>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5016 (20100410) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
--- End Message ---
--- Begin Message ---
Hi,
I'm experiencing very slow performance of my php scripts ... At first and
for a long time I thought it was Oracle
fault cause I didn't use binding (I rewrote the code ), but the performance
is still bad.
So I tested my sql statement and did some time measurement and found out
that Oracle can fetch 300 records in less
then 2 seconds, but PHP needs 210seconds ... to display it to the user ....
I'm using default php.ini
I have a horizontal menu with buttons to switch pages ..... and just
switching pages takes time with minimum or no oracle interactions.
My php version: 5.2.6
OS: Windows XP sP3 and Windows Server 2008
Testing it in PHPEd using internal PHPEd server and FastCGI ... and also on
Windows 2008 server in IIS ...same results .... both using the same
php.ini....
Would anyone know what could be the cause of this ?
Any hit is appreciated.
Kris
--- End Message ---
--- Begin Message ---
one more thing ... I use lot of includes in the code to display some
specific content....
BR,
Kris
""Kristijan Marin"" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
>
>
> I'm experiencing very slow performance of my php scripts ... At first and
> for a long time I thought it was Oracle
>
> fault cause I didn't use binding (I rewrote the code ), but the
> performance is still bad.
>
> So I tested my sql statement and did some time measurement and found out
> that Oracle can fetch 300 records in less
>
> then 2 seconds, but PHP needs 210seconds ... to display it to the user
> .... I'm using default php.ini
>
>
>
> I have a horizontal menu with buttons to switch pages ..... and just
> switching pages takes time with minimum or no oracle interactions.
>
>
>
> My php version: 5.2.6
>
> OS: Windows XP sP3 and Windows Server 2008
>
>
>
> Testing it in PHPEd using internal PHPEd server and FastCGI ... and also
> on Windows 2008 server in IIS ...same results .... both using the same
> php.ini....
>
>
>
> Would anyone know what could be the cause of this ?
>
>
>
> Any hit is appreciated.
>
>
>
> Kris
>
>
--- End Message ---
--- Begin Message ---
have you tried using a profiler like xdebug? or may be it is not a php
issue at all..
i find https://addons.mozilla.org/en-US/firefox/addon/1843 to be
helpful in these situations
--- End Message ---
--- Begin Message ---
Given a MySQL query like this $q = "select num from table", I get a result
like this:
+---+
|num|
+---+
| 1|
| 4|
| 6|
| 2|
| 4|
| 5|
| 3|
| 2|
| 4|
| 2|
| 3|
| 3|
| 2|
| 1|
+---+
What I want is a listing of numbers sorted by the amount of times they
appear (so I can take a top 5, or top 10):
+---+-----+
|num|count|
+---+-----+
| 2| 4|
| 3| 3|
| 4| 3|
| 1| 2|
| 5| 1|
| 6| 1|
+---+-----+
Is this a query that I can feed to MySQL, or is this something I need to
sort out in PHP?
--- End Message ---
--- Begin Message ---
Ashley M. Kirchner wrote:
Given a MySQL query like this $q = "select num from table", I get a result
like this:
+---+
|num|
+---+
| 1|
| 4|
| 6|
| 2|
| 4|
| 5|
| 3|
| 2|
| 4|
| 2|
| 3|
| 3|
| 2|
| 1|
+---+
What I want is a listing of numbers sorted by the amount of times they
appear (so I can take a top 5, or top 10):
+---+-----+
|num|count|
+---+-----+
| 2| 4|
| 3| 3|
| 4| 3|
| 1| 2|
| 5| 1|
| 6| 1|
+---+-----+
Is this a query that I can feed to MySQL, or is this something I need to
sort out in PHP?
Dunno, is this relevant? http://lists.mysql.com/mysql/25829
kdk
--- End Message ---
--- Begin Message ---
Eh, he's off topic, but we've talked plenty SQL on here before...
SELECT num, COUNT( num ) FROM table GROUP BY num;
I don't have a myqsl server to test that, but should do it.
--
-Dan Joseph
www.canishosting.com - Unlimited Hosting Plans start @ $3.95/month. Promo
Code "NEWTHINGS" for 10% off initial order
http://www.facebook.com/canishosting
http://www.facebook.com/originalpoetry
--- End Message ---
--- Begin Message ---
If you want to take top 5:
SELECT num, COUNT( num ) as c FROM table GROUP BY num order by c desc limit
5
Thanks,
Vikash Kumar
--
http://vika.sh
On Sun, Apr 11, 2010 at 8:07 AM, Dan Joseph <[email protected]> wrote:
> Eh, he's off topic, but we've talked plenty SQL on here before...
>
> SELECT num, COUNT( num ) FROM table GROUP BY num;
>
> I don't have a myqsl server to test that, but should do it.
>
> --
> -Dan Joseph
>
> www.canishosting.com - Unlimited Hosting Plans start @ $3.95/month. Promo
> Code "NEWTHINGS" for 10% off initial order
>
> http://www.facebook.com/canishosting
> http://www.facebook.com/originalpoetry
>
--- End Message ---
--- Begin Message ---
On Apr 10, 2010, at 10:02 PM, Ashley M. Kirchner wrote:
>
>
> Given a MySQL query like this $q = "select num from table", I get a result
> like this:
>
>
>
> +---+
>
> |num|
>
> +---+
>
> | 1|
>
> | 4|
>
> | 6|
>
> | 2|
>
> | 4|
>
> | 5|
>
> | 3|
>
> | 2|
>
> | 4|
>
> | 2|
>
> | 3|
>
> | 3|
>
> | 2|
>
> | 1|
>
> +---+
>
>
>
> What I want is a listing of numbers sorted by the amount of times they
> appear (so I can take a top 5, or top 10):
>
>
>
> +---+-----+
>
> |num|count|
>
> +---+-----+
>
> | 2| 4|
>
> | 3| 3|
>
> | 4| 3|
>
> | 1| 2|
>
> | 5| 1|
>
> | 6| 1|
>
> +---+-----+
>
>
>
> Is this a query that I can feed to MySQL, or is this something I need to
> sort out in PHP?
>
This query should do it for you:
SELECT
num,
COUNT(num) AS total
FROM
table
GROUP BY
num
ORDER BY
COUNT(num) DESC
LIMIT 10
Take care,
Floyd
--- End Message ---
