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

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Re: Inserting into multiple tables

--00148531a8a444ef3e0483e901d9
Content-Type: text/plain; charset=ISO-8859-1

On Sat, Apr 10, 2010 at 5:27 PM, Gary 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

--00148531a8a444ef3e0483e901d9--

Re: Inserting into multiple tables

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" wrote in message
news:x2ge4d8ea9d1004101457r62b7c0b8uc3673353c3758394@mail.gm ail.com...
> On Sat, Apr 10, 2010 at 5:27 PM, Gary 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

Re: Inserting into multiple tables

--0016362570a1b99d7b0483ed5a05
Content-Type: text/plain; charset=ISO-8859-1

On Sat, Apr 10, 2010 at 7:56 PM, Gary 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" wrote in message
> news:x2ge4d8ea9d1004101457r62b7c0b8uc3673353c3758394@mail.gm ail.com...
>
>> On Sat, Apr 10, 2010 at 5:27 PM, Gary 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-normal ization.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/06723 29166/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

--0016362570a1b99d7b0483ed5a05--

Re: Inserting into multiple tables

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" wrote in message
news:i2je4d8ea9d1004102009r963ef9f3i930a2e757b5ce24a@mail.gm ail.com...
> On Sat, Apr 10, 2010 at 7:56 PM, Gary 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" wrote in message
>> news:x2ge4d8ea9d1004101457r62b7c0b8uc3673353c3758394@mail.gm ail.com...
>>
>>> On Sat, Apr 10, 2010 at 5:27 PM, Gary 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-normal ization.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/06723 29166/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





--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Inserting into multiple tables

Hi Gary,

> -----Original Message-----
> From: Gary [mailto:gwpaul@ptd.net]
> Sent: Saturday, April 10, 2010 2:28 PM
> To: php-general@lists.php.net
> Subject: [PHP] Inserting into multiple tables
>=20
> 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).
>=20
> Someone point me in the right direction?
>=20
> Gary
>=20
> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>
>
> charset=3Dutf-8" />
>
>
>=20
>
>=20
>

Re: Inserting into multiple tables

Tommy

Thanks for your reply. The code you had read I was trying to concatonate
the insert commands without the semicolon at the end....I had also tried
using the semicolons on each line...same result.

I am reading about the mysqli_multi_query now, so far I am not getting the
results. Interestingly, it lead me to the mysqli_store_result(), however it
said it returned a false result on the insert command.

Thank you for your reply...

Gary


""Tommy Pham"" wrote in message
news:013601cad93e$e0bca6a0$a235f3e0$@com...
Hi Gary,

> -----Original Message-----
> From: Gary [mailto:gwpaul@ptd.net]
> Sent: Saturday, April 10, 2010 2:28 PM
> To: php-general@lists.php.net
> 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
>
> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>
>
>
>
>
>
>
>
>

Re: Inserting into multiple tables

Gary wrote:
> 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?
>

I'm unsure if this is of use to you or not (and it has been covered in
part already), but here goes:

Generally when working with database tables, we normalise, or split
information up at natural points where you have a greater than 1-1
relationships between the data items.

An example may be a table structure to store User, Blog Post and Comments.

With this common example it's very inefficient to store all the
information in one table, because it is split naturally in to three.

We have three relationships here; all 1-* (meaning "one to many").

1 User to * Blog Posts
1 Blog Post to * Comments

Thus naturally, and on first glance we would have 3 tables:

| Table User
--------------------------------------
| UserID | Username | Password | ...


| Table BlogPost
--------------------------------------
| PostID | PostTitle | PostersUserID | ...


| Table Comment
--------------------------------------
| CommentID | CommentOnPostID | CommentersUserID | ...


As you can see from the above, all of the rows (or items) in our tables
are linked via IDs to each other.

We can further normalise the above tables to take in to account *-*
(many to many) type relationships, and to fully separate cross cutting
concerns. For instance it may be that a BlogPost has 3 different Users
as author(s).

aside: cross cutting concerns can be considered as something (in this
case a table) trying to handle something which is of no concern to it
(in this case the BlogPost table needs to be aware of Users and their IDs).

To handle the aforementioned we can introduce something commonly
referred to as "link tables", consider:


| Table User
--------------------------------------
| UserID | Username | Password | ...


| Table BlogPost
--------------------------------------
| PostID | PostTitle | ...


| Table BlogPostAuthors
--------------------------------------
| BlogPostID | UserID |


The "link table" BlogPostAuthors acts as a many-to-many join table
between BlogPosts and Users. Similarly we could introduce the same kind
of link table between BlogPosts and Comments, / Users and Comments.

In a real system we may even have another two primary tables introduced,
Roles and UserRoles, as the system may have multiple Roles (Author,
Commenter, Admin etc) and each User may have multiple Roles themselves,
in one capacity I am an Admin, in another I'm an Author. (UserRoles may
be better considered as Personas?)

Ultimately there are many considerations to take in to account, the
relationships between types of data, the frequency at which
inserts/updates/selects occur, the complexity and speed of each query,
and much more.

Designing a table structure is different for each job, with different
considerations and things to weigh up, generally though normalisation
can cater for at least some future scope creep.

It's also worth noting that some consider it bad practise to design a
system from the storage point upwards, because the application and data
should not be constrained by persistence layer features or limitations -
which would indicate designing the data model in UML or suchlike and
dealing with Objects rather than Tables (then later mapping objects to
tables in order to persist them, if choosing a RDBMS as the persistence
layer).

It may also be worth noting that an EAV model is the ultimate in
normalisation and allows all data to be persisted in a single 3 column
structure (or 4 if you partition data). I'll save details of this though.

Do hope that helps in some way, and if you need any more info just shout.

Nathan

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Inserting into multiple tables

On Sun, Apr 11, 2010 at 12:21:28AM -0400, Gary wrote:




>
> 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?

Yes. The emphasis on multiple tables is mostly to ensure data integrity.
It goes back to the beginning of relational databases. And where you
have multiple tables, the most difficult task (and the one which takes
up the most pages in texts) is queries on those multiple tables.
Insertions are considered elementary, and they are typically done one
table at a time. You'll notice the syntax for queries is considerably
more complex than that for inserts, because the task is more complex.

Paul

--
Paul M. Foster

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Inserting into multiple tables

Nathan

Thank you for your excellent explanation! One of the reasons I love this
board is the vast knowledge that people are willing to share.

I believe I understand the importance of normalization, however one of my
original questions seems to still stand.

If normalization is so important, why is it that the INSERT INTO multiple
tables is not a standard command or procedure?. I'm not saying it has to be
easy, but it should be well known. I really thought when I first asked the
question I was going to get multiple similar answers, or someone was going
to look at my script and tell me I omitted some simple puncuatuion (or other
simple mistake)...which has not been the case.

Even if the answer were "Cant be done", you need to write a separate script
for each insert, that would be ok. But I have to think that someone reading
this board has accomplished, somehow, writing to separate tables in the same
DB.

Again, thank you for all the information and your time.

Gary


"Nathan Rixham" wrote in message
news:4BC21B88.3090408@gmail.com...
> Gary wrote:
>> 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?
>>
>
> I'm unsure if this is of use to you or not (and it has been covered in
> part already), but here goes:
>
> Generally when working with database tables, we normalise, or split
> information up at natural points where you have a greater than 1-1
> relationships between the data items.
>
> An example may be a table structure to store User, Blog Post and Comments.
>
> With this common example it's very inefficient to store all the
> information in one table, because it is split naturally in to three.
>
> We have three relationships here; all 1-* (meaning "one to many").
>
> 1 User to * Blog Posts
> 1 Blog Post to * Comments
>
> Thus naturally, and on first glance we would have 3 tables:
>
> | Table User
> --------------------------------------
> | UserID | Username | Password | ...
>
>
> | Table BlogPost
> --------------------------------------
> | PostID | PostTitle | PostersUserID | ...
>
>
> | Table Comment
> --------------------------------------
> | CommentID | CommentOnPostID | CommentersUserID | ...
>
>
> As you can see from the above, all of the rows (or items) in our tables
> are linked via IDs to each other.
>
> We can further normalise the above tables to take in to account *-*
> (many to many) type relationships, and to fully separate cross cutting
> concerns. For instance it may be that a BlogPost has 3 different Users
> as author(s).
>
> aside: cross cutting concerns can be considered as something (in this
> case a table) trying to handle something which is of no concern to it
> (in this case the BlogPost table needs to be aware of Users and their
> IDs).
>
> To handle the aforementioned we can introduce something commonly
> referred to as "link tables", consider:
>
>
> | Table User
> --------------------------------------
> | UserID | Username | Password | ...
>
>
> | Table BlogPost
> --------------------------------------
> | PostID | PostTitle | ...
>
>
> | Table BlogPostAuthors
> --------------------------------------
> | BlogPostID | UserID |
>
>
> The "link table" BlogPostAuthors acts as a many-to-many join table
> between BlogPosts and Users. Similarly we could introduce the same kind
> of link table between BlogPosts and Comments, / Users and Comments.
>
> In a real system we may even have another two primary tables introduced,
> Roles and UserRoles, as the system may have multiple Roles (Author,
> Commenter, Admin etc) and each User may have multiple Roles themselves,
> in one capacity I am an Admin, in another I'm an Author. (UserRoles may
> be better considered as Personas?)
>
> Ultimately there are many considerations to take in to account, the
> relationships between types of data, the frequency at which
> inserts/updates/selects occur, the complexity and speed of each query,
> and much more.
>
> Designing a table structure is different for each job, with different
> considerations and things to weigh up, generally though normalisation
> can cater for at least some future scope creep.
>
> It's also worth noting that some consider it bad practise to design a
> system from the storage point upwards, because the application and data
> should not be constrained by persistence layer features or limitations -
> which would indicate designing the data model in UML or suchlike and
> dealing with Objects rather than Tables (then later mapping objects to
> tables in order to persist them, if choosing a RDBMS as the persistence
> layer).
>
> It may also be worth noting that an EAV model is the ultimate in
> normalisation and allows all data to be persisted in a single 3 column
> structure (or 4 if you partition data). I'll save details of this though.
>
> Do hope that helps in some way, and if you need any more info just shout.
>
> Nathan
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5021 (20100412) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>



__________ Information from ESET Smart Security, version of virus signature database 5021 (20100412) __________

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

Re: Inserting into multiple tables

At 11:05 AM -0400 4/12/10, Gary wrote:
-snip-
>If normalization is so important, why is it that the INSERT INTO multiple
>tables is not a standard command or procedure?.

In my view, you are mixing apples and oranges.

"Normalization" is simply cutting down on repetition. Inserting data
into multiple tables is a different critter -- it doesn't make
"Normalization" any better or worse.

Just figure out what data you need to acquire, what tables you need
to store it in, and then what references you need to what
tables/fields to use it while reducing repetition.

For example if you have a customer table, you don't need to add all
the customer data to each sales receipt (sales table) when you can
simply record the customer's ID.

Likewise with the items that are sold, you don't need to include all
the items attributes in the sales receipt (sales table) when you can
simply record the items' ID.

None of the above requires some special way to inserting data into
multiple tables -- you just record the sales.

Sales table

Sales ID -- Customer ID -- Item ID -- and probably the date.
1234 -- 6789 -- 101112131415 -- 4/12/10

Cheers,

tedd


--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Inserting into multiple tables

Tedd

Thanks for your response, perhaps I was completely missing this or my powers
of expanation have gone out the window.

I have found a solution and I am going to put it in its own post.

Thanks again.

gary


"tedd" wrote in message
news:p0624080ac7e9123f4856@[192.168.1.102]...
> At 11:05 AM -0400 4/12/10, Gary wrote:
> -snip-
>>If normalization is so important, why is it that the INSERT INTO multiple
>>tables is not a standard command or procedure?.
>
> In my view, you are mixing apples and oranges.
>
> "Normalization" is simply cutting down on repetition. Inserting data into
> multiple tables is a different critter -- it doesn't make "Normalization"
> any better or worse.
>
> Just figure out what data you need to acquire, what tables you need to
> store it in, and then what references you need to what tables/fields to
> use it while reducing repetition.
>
> For example if you have a customer table, you don't need to add all the
> customer data to each sales receipt (sales table) when you can simply
> record the customer's ID.
>
> Likewise with the items that are sold, you don't need to include all the
> items attributes in the sales receipt (sales table) when you can simply
> record the items' ID.
>
> None of the above requires some special way to inserting data into
> multiple tables -- you just record the sales.
>
> Sales table
>
> Sales ID -- Customer ID -- Item ID -- and probably the date.
> 1234 -- 6789 -- 101112131415 -- 4/12/10
>
> Cheers,
>
> tedd
>
>
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5022 (20100412) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>



__________ Information from ESET Smart Security, version of virus signature database 5023 (20100412) __________

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

Solution

For those that were looking to see a solution, this is what I have come up
with. It was pointed out on another board (MySQL) that inserting multiple
in one script is probably prohibited because of security reasons.

What I did was open the connection, insert into the table, close the
connection, close the php script, then start over again. This is the code:

$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
MySQL server');

$query="INSERT INTO name(fname, lname)"."VALUES('$fname','$lname')";

$result=mysqli_query($dbc, $query)
or die('Error querying database.');

mysqli_close($dbc);
?>


$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
MySQL server');
$query="INSERT INTO address (street, town, state,
zip)"."VALUES('$street','$town','$state','$zip')";

$result=mysqli_query($dbc, $query)
or die('Error querying database.');

mysqli_close($dbc);

?>

It seems a little redundant for PHP, however it seems to work.

Thank you to everyone that responded. If by the way someone sees an issue
with this solution, I would love to read it.

Gary


""Gary"" wrote in message
news:C7.00.11452.A84E1CB4@pb1.pair.com...
> Tommy
>
> Thanks for your reply. The code you had read I was trying to concatonate
> the insert commands without the semicolon at the end....I had also tried
> using the semicolons on each line...same result.
>
> I am reading about the mysqli_multi_query now, so far I am not getting the
> results. Interestingly, it lead me to the mysqli_store_result(), however
> it said it returned a false result on the insert command.
>
> Thank you for your reply...
>
> Gary
>
>
> ""Tommy Pham"" wrote in message
> news:013601cad93e$e0bca6a0$a235f3e0$@com...
> Hi Gary,
>
>> -----Original Message-----
>> From: Gary [mailto:gwpaul@ptd.net]
>> Sent: Saturday, April 10, 2010 2:28 PM
>> To: php-general@lists.php.net
>> 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
>>
>> >> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
>>
>>
>>
>>
>>
>>
>>
>>
>>

Re: Solution

On 13 April 2010 00:04, Gary wrote:
> For those that were looking to see a solution, this is what I have come u=
p
> with.  It was pointed out on another board (MySQL) that inserting mu=
ltiple
> in one script is probably prohibited because of security reasons.
>
> What I did was open the connection, insert into the table, close the
> connection, close the php script, then start over again.  This is th=
e code:
>
> $dbc=3Dmysqli_connect('localhost','root','','test')or die('Error connecti=
ng to
> MySQL server');
>
> $query=3D"INSERT INTO name(fname, lname)"."VALUES('$fname','$lname')";
>
> $result=3Dmysqli_query($dbc, $query)
> or die('Error querying database.');
>
> mysqli_close($dbc);
> ?>
>
> >
> $dbc=3Dmysqli_connect('localhost','root','','test')or die('Error connecti=
ng to
> MySQL server');
> $query=3D"INSERT INTO address (street, town, state,
> zip)"."VALUES('$street','$town','$state','$zip')";
>
> $result=3Dmysqli_query($dbc, $query)
> or die('Error querying database.');
>
> mysqli_close($dbc);
>
> ?>
>
> It seems a little redundant for PHP, however it seems to work.
>
> Thank you to everyone that responded.  If by the way someone sees an=
issue
> with this solution, I would love to read it.

Off the top of my head: just reuse the connection. There's no need to
close it, then reopen it. The only security problem you're facing is
that you cannot send multiple queries in *the same string*[1]. So send
the queries one by one, but in the same script, using the same
connection.

1. The reason this is a security concern is that otherwise, should
someone manage to inject sql into your query, they could drop in a
semi-colon and then start a new query. By not allowing this, a lot of
bad injections are by default ruled out.

--=20

WWW: http://plphp.dk / http://plind.dk
LinkedIn: http://www.linkedin.com/in/plind
Flickr: http://www.flickr.com/photos/fake51
BeWelcome: Fake51
Couchsurfing: Fake51


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

Hey Gary, instead try something like this maybe?


$dbc=mysqli_connect('localhost','root','','test') or die('Error
connecting to MySQL server');

$query_name="INSERT INTO name(fname='$fname', lname='$lname')";
$query_address="INSERT INTO address (street='$street', town='$town',
state='$state', zip='$zip')";

$result1=mysqli_query($dbc, $query_name) or die('Error querying
database for name.');

$result2=mysqli_query($dbc, $query_address) or die('Error querying
database for address.');

echo "Success!
";

?>

HTH,

Karl


On Apr 12, 2010, at 5:16 PM, Peter Lind wrote:

> $dbc=mysqli_connect('localhost','root','','test')or die('Error
> connecting to
> MySQL server');
>
> $query="INSERT INTO name(fname, lname)"."VALUES('$fname','$lname')";
>
> $result=mysqli_query($dbc, $query)
> or die('Error querying database.');
>
> mysqli_close($dbc);
> ?>
>
> >
> $dbc=mysqli_connect('localhost','root','','test')or die('Error
> connecting to
> MySQL server');
> $query="INSERT INTO address (street, town, state,
> zip)"."VALUES('$street','$town','$state','$zip')";
>
> $result=mysqli_query($dbc, $query)
> or die('Error querying database.');
>
> mysqli_close($dbc);
>
> ?>

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

Gary wrote:
> For those that were looking to see a solution

[snip]

If anybody really wants to know how to handle this, (it's not officially
supported by PHP, but is in MySQL 5.1+, and I'm not condoning it) here's
how you do it:


// enable C API support for multiple statement execution
// note the 65536 flag
$connection = mysql_connect( $host, $user , $password , true , 65536 );

// select the database
mysql_select_db( 'test' , $connection );

// line up multiple queries separated by a semicolon (;)
$query1 = "DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(id INT);
INSERT INTO test_table VALUES(10);
UPDATE test_table SET id=20 WHERE id=10;";

// run the multi-query (should output bool(true) on success)
var_dump( mysql_query( $query1 , $connection ) );

// big caveat!, the connection will die so we need to reconnect..
$connection = mysql_connect( $host, $user , $password , true , 65536 );
mysql_select_db( 'test' , $connection );

// now a quick test to verify
$test_result = mysql_query( "SELECT * FROM test_table" , $connection );
$result_rows = mysql_num_rows( $test_result );
if ($result_rows == 0) {
throw new Exception( 'we failed' );
}
for ($r=0;$r<$result_rows;$r++) {
print_r( mysql_fetch_assoc( $test_result ) );
}
mysql_free_result( $test_result );

?>


Probably worth somebody investigating this further and seeing how it
works with mysqli etc (unsure if mysqli users mysql_connect or
mysql_real_connect at the C API level.)

Regards,

Nathan

http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-querie s.html


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

At 6:04 PM -0400 4/12/10, Gary wrote:
>For those that were looking to see a solution, this is what I have come up
>with. It was pointed out on another board (MySQL) that inserting multiple
>in one script is probably prohibited because of security reasons.
>
>What I did was open the connection, insert into the table, close the
>connection, close the php script, then start over again. This is the code:
>
>$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
>MySQL server');
>
>$query="INSERT INTO name(fname, lname)"."VALUES('$fname','$lname')";
>
>$result=mysqli_query($dbc, $query)
>or die('Error querying database.');
>
>mysqli_close($dbc);
>?>
>
> >
>$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
>MySQL server');
>$query="INSERT INTO address (street, town, state,
>zip)"."VALUES('$street','$town','$state','$zip')";
>
>$result=mysqli_query($dbc, $query)
>or die('Error querying database.');
>
>mysqli_close($dbc);
>
>?>
>
>It seems a little redundant for PHP, however it seems to work.
>
>Thank you to everyone that responded. If by the way someone sees an issue
>with this solution, I would love to read it.
>
>Gary

Gary :

It not only looks redundant, but why two tables?

Why not "customers" or "users" or "subscribers" like so:

include(opendb.php);

$query="INSERT INTO users (first_name, last_name, street, town, state, zip)
VALUES('$first_name', '$last_name', $street', '$town', '$state', '$zip')";

$result=mysqli_query($dbc, $query) or die('Error querying database.');

include(closedb.php);

I don't see any reason to separate the attributes of the person into
two different tables. Why do that?

Cheers,

tedd

--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

Tedd

I had four tables. name, (fname, lname) address(street, town, state, zip),
contact(phone, fax, email), comments (comments).

It was done this way because it is strictly a learning exercise. I had
never created a DB with multiple tables, so I wanted to be able to contruct
one using foreign keys, and be able to insert and retrieve from it.

But the experiment continues. For whatever reason, scripts to query a DB
that I have used for years are not working. I am thinking it has something
to do with that I switched engines to innodb.

So the lesson continues.

Gary

"tedd" wrote in message
news:p06240804c7ea44eb9789@[192.168.1.102]...
> At 6:04 PM -0400 4/12/10, Gary wrote:
>>For those that were looking to see a solution, this is what I have come up
>>with. It was pointed out on another board (MySQL) that inserting multiple
>>in one script is probably prohibited because of security reasons.
>>
>>What I did was open the connection, insert into the table, close the
>>connection, close the php script, then start over again. This is the
>>code:
>>
>>$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting
>>to
>>MySQL server');
>>
>>$query="INSERT INTO name(fname, lname)"."VALUES('$fname','$lname')";
>>
>>$result=mysqli_query($dbc, $query)
>>or die('Error querying database.');
>>
>>mysqli_close($dbc);
>>?>
>>
>> >>
>>$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting
>>to
>>MySQL server');
>>$query="INSERT INTO address (street, town, state,
>>zip)"."VALUES('$street','$town','$state','$zip')";
>>
>>$result=mysqli_query($dbc, $query)
>>or die('Error querying database.');
>>
>>mysqli_close($dbc);
>>
>>?>
>>
>>It seems a little redundant for PHP, however it seems to work.
>>
>>Thank you to everyone that responded. If by the way someone sees an issue
>>with this solution, I would love to read it.
>>
>>Gary
>
> Gary :
>
> It not only looks redundant, but why two tables?
>
> Why not "customers" or "users" or "subscribers" like so:
>
> include(opendb.php);
>
> $query="INSERT INTO users (first_name, last_name, street, town, state,
> zip)
> VALUES('$first_name', '$last_name', $street', '$town', '$state', '$zip')";
>
> $result=mysqli_query($dbc, $query) or die('Error querying database.');
>
> include(closedb.php);
>
> I don't see any reason to separate the attributes of the person into two
> different tables. Why do that?
>
> Cheers,
>
> tedd
>
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5025 (20100413) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>



__________ Information from ESET Smart Security, version of virus signature database 5025 (20100413) __________

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

Re: Solution

tedd wrote:
>
> I don't see any reason to separate the attributes of the person into two
> different tables. Why do that?
>

tedd,

Funny you ask this. I have always thought the same thing. Then just last week
I started listing to the Zend Dev Zone podcasts. I came across this one and
thought it was very informative.

http://devzone.zend.com/article/4497-The-ZendCon-Sessions-Ep isode-17-SQL-Query-Tuning-The-Legend-of-Drunken-Query-Master

It is pretty long, but it contains a lot of good information.

Listen the part about how many records you can contain in memory.

--
Jim Lucas

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

At 12:26 PM -0400 4/13/10, Gary wrote:
>Tedd
>
>I had four tables. name, (fname, lname) address(street, town, state, zip),
>contact(phone, fax, email), comments (comments).
>
>It was done this way because it is strictly a learning exercise. I had
>never created a DB with multiple tables, so I wanted to be able to contruct
>one using foreign keys, and be able to insert and retrieve from it.
>
>But the experiment continues. For whatever reason, scripts to query a DB
>that I have used for years are not working. I am thinking it has something
>to do with that I switched engines to innodb.
>
>So the lesson continues.
>
>Gary

Gary:

One of the concepts in OOP is identifying an object in terms of it's
attributes and functions. The concept of defining tables is very
similar.

If you want to learn about foreign keys, then think of them as things
that are foreign and not things that are similar.

For example, a user has name, address, height, weight, color,
language, etc. Those things can be listed in a single table.

A item that can be purchased, like a chair has a title, description,
weight, color, cost and such. Those things can also be listed in a
single table.

If a person buys a chair and the seller wants to create a record of
the purchase, then all the seller needs to do is to record the
buyer's and the chair's attributes. Now, the seller can either write
down all the attributes for each buyer/chair sale OR reference them
using foreign keys.

User Table
id = 123
name = tedd
address = whatever

Item Table
id = 456
name = chair
color = whatever

Sales Table
item sold = 456
buyer = 123

Done!

Get the idea?

Cheers,

tedd


--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

Karl

Thanks for the reply, I copied your code and it did not work, tried a few
variations and same result.

Gary
"Karl DeSaulniers" wrote in message
news:E56BE001-63F6-4DF1-8C72-AD468C23EBBE@designdrumm.com...
> Hey Gary, instead try something like this maybe?
>
>
> $dbc=mysqli_connect('localhost','root','','test') or die('Error
> connecting to MySQL server');
>
> $query_name="INSERT INTO name(fname='$fname', lname='$lname')";
> $query_address="INSERT INTO address (street='$street', town='$town',
> state='$state', zip='$zip')";
>
> $result1=mysqli_query($dbc, $query_name) or die('Error querying database
> for name.');
>
> $result2=mysqli_query($dbc, $query_address) or die('Error querying
> database for address.');
>
> echo "Success!
";
>
> ?>
>
> HTH,
>
> Karl
>
>
> On Apr 12, 2010, at 5:16 PM, Peter Lind wrote:
>
>> $dbc=mysqli_connect('localhost','root','','test')or die('Error
>> connecting to
>> MySQL server');
>>
>> $query="INSERT INTO name(fname, lname)"."VALUES('$fname','$lname')";
>>
>> $result=mysqli_query($dbc, $query)
>> or die('Error querying database.');
>>
>> mysqli_close($dbc);
>> ?>
>>
>> >>
>> $dbc=mysqli_connect('localhost','root','','test')or die('Error
>> connecting to
>> MySQL server');
>> $query="INSERT INTO address (street, town, state,
>> zip)"."VALUES('$street','$town','$state','$zip')";
>>
>> $result=mysqli_query($dbc, $query)
>> or die('Error querying database.');
>>
>> mysqli_close($dbc);
>>
>> ?>
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5023 (20100412) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>



__________ Information from ESET Smart Security, version of virus signature database 5026 (20100413) __________

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

Re: Solution

On Tue, Apr 13, 2010 at 2:50 PM, tedd wrote:
> For example, a user has name, address, height, weight, color, language, etc.
> Those things can be listed in a single table.

And now for the universal DBA answer - it depends. There are cases
where that information needs to be normalized to another table. A
person could have a billing address, a shipping address, a vacation
address, etc. Or the application may need to store multiple addresses
over time. The same person could have a home phone, office phone, fax,
mobile phone, pager, etc.

Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

Karl DeSaulniers wrote:
> Hey Gary, instead try something like this maybe?
>
>
> $dbc=mysqli_connect('localhost','root','','test') or die('Error
> connecting to MySQL server');
>
> $query_name="INSERT INTO name(fname='$fname', lname='$lname')";
> $query_address="INSERT INTO address (street='$street', town='$town',
> state='$state', zip='$zip')";

Their are a few syntax variations available, but the one that they used is not a
valid format. The following are the insert statements written in a valid format.

$query_name = "INSERT INTO name (
fname, lname
) VALUES (
'$fname', '$lname'
)";

$query_address = "INSERT INTO address (
street, town, state, zip
) VALUES {
'$street', '$town', '$state', '$zip'
)";

Try this, it should work.

>
> $result1=mysqli_query($dbc, $query_name) or die('Error querying database
> for name.');
>
> $result2=mysqli_query($dbc, $query_address) or die('Error querying
> database for address.');
>
> echo "Success!
";
>
> ?>
>
> HTH,
>
> Karl
>
>
> On Apr 12, 2010, at 5:16 PM, Peter Lind wrote:
>
>> $dbc=mysqli_connect('localhost','root','','test')or die('Error
>> connecting to
>> MySQL server');
>>
>> $query="INSERT INTO name(fname, lname)"."VALUES('$fname','$lname')";
>>
>> $result=mysqli_query($dbc, $query)
>> or die('Error querying database.');
>>
>> mysqli_close($dbc);
>> ?>
>>
>> >>
>> $dbc=mysqli_connect('localhost','root','','test')or die('Error
>> connecting to
>> MySQL server');
>> $query="INSERT INTO address (street, town, state,
>> zip)"."VALUES('$street','$town','$state','$zip')";
>>
>> $result=mysqli_query($dbc, $query)
>> or die('Error querying database.');
>>
>> mysqli_close($dbc);
>>
>> ?>
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>


--
Jim Lucas

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

Andrew Ballard wrote:
> On Tue, Apr 13, 2010 at 2:50 PM, tedd wrote:
>> For example, a user has name, address, height, weight, color, language, etc.
>> Those things can be listed in a single table.
>
> And now for the universal DBA answer - it depends. There are cases
> where that information needs to be normalized to another table. A
> person could have a billing address, a shipping address, a vacation
> address, etc. Or the application may need to store multiple addresses
> over time. The same person could have a home phone, office phone, fax,
> mobile phone, pager, etc.
>
> Andrew
>

My business related example is our billing system in my office.

For each customer account we have a single Billing address but allow multiple
service, technical, administrative, etc... contact entries. We must keep past
entries for historical purposes too. This is achieved by having two separate
tables to hold the data. It is a 1 to nth relationship.

PS: Gary, fix your mail server. It is telling me that you don't exist.

--
Jim Lucas

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

At 3:09 PM -0400 4/13/10, Andrew Ballard wrote:
>On Tue, Apr 13, 2010 at 2:50 PM, tedd wrote:
>> For example, a user has name, address, height, weight, color, language, etc.
>> Those things can be listed in a single table.
>
>And now for the universal DBA answer - it depends. There are cases
>where that information needs to be normalized to another table. A
>person could have a billing address, a shipping address, a vacation
>address, etc. Or the application may need to store multiple addresses
>over time. The same person could have a home phone, office phone, fax,
>mobile phone, pager, etc.
>
>Andrew


Andrew:

Of course.

Cheers.

tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Solution

My mistake. You are correct.

Karl


On Apr 13, 2010, at 2:16 PM, Jim Lucas wrote:

Karl DeSaulniers wrote:
> Hey Gary, instead try something like this maybe?
>
>
> $dbc=mysqli_connect('localhost','root','','test') or die('Error
> connecting to MySQL server');
>
> $query_name="INSERT INTO name(fname='$fname', lname='$lname')";
> $query_address="INSERT INTO address (street='$street', town='$town',
> state='$state', zip='$zip')";

Their are a few syntax variations available, but the one that they
used is not a
valid format. The following are the insert statements written in a
valid format.

$query_name = "INSERT INTO name (
fname, lname
) VALUES (
'$fname', '$lname'
)";

$query_address = "INSERT INTO address (
street, town, state, zip
) VALUES {
'$street', '$town', '$state', '$zip'
)";

Try this, it should work.

>
> $result1=mysqli_query($dbc, $query_name) or die('Error querying
> database
> for name.');
>
> $result2=mysqli_query($dbc, $query_address) or die('Error querying
> database for address.');
>
> echo "Success!
";
>
> ?>
>
> HTH,
>
> Karl
>
>
> On Apr 12, 2010, at 5:16 PM, Peter Lind wrote:
>
>> $dbc=mysqli_connect('localhost','root','','test')or die('Error
>> connecting to
>> MySQL server');
>>
>> $query="INSERT INTO name(fname, lname)"."VALUES('$fname','$lname')";
>>
>> $result=mysqli_query($dbc, $query)
>> or die('Error querying database.');
>>
>> mysqli_close($dbc);
>> ?>
>>
>> >>
>> $dbc=mysqli_connect('localhost','root','','test')or die('Error
>> connecting to
>> MySQL server');
>> $query="INSERT INTO address (street, town, state,
>> zip)"."VALUES('$street','$town','$state','$zip')";
>>
>> $result=mysqli_query($dbc, $query)
>> or die('Error querying database.');
>>
>> mysqli_close($dbc);
>>
>> ?>
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>


--
Jim Lucas

"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php