Hello,

Excel has a feature called 'split' then 'freeze panes' that allows a user to
freeze column headers where showing a column title might be important as the
page is scrolled through.

Is that possible to do with PHP?  I'm using the following in a query and would
really like to lock the header titles where as the page is scrolled through,
what that particular column is indicating isn't lost, just as it wouldn't be if
a person were using the 'split' feature in Excel.  Thanks very much for any
help.  John


print "<h2>There are $number records in the database:</h2>
     <table cellpadding=5>
     <tr bgcolor=black>
       <td><font color=white><b>Name</b></td></font></td>
       <td><font color=white><b>Year</b></td></font></td>
       <td><font color=white><b>Date</b></font></td>
       <td><font color=white><b>Event</b></td></font></td>";
for($i=0; $i<$number; $i++) {
     $name = mysql_result($result,$i,"name");
     $year = mysql_result($result,$i,"year");
     $date = mysql_result($result,$i,"date");
     $event = mysql_result($result,$i,"event");
     /* print even-numbered rows with a grey background,
            odd-numbered with a white background */
         if ($i%2 == 0) {
         print "<tr bgcolor=lightgrey>";
         } else {
         print "<tr>";
         }
     print "<td>$name</td>
     <td>$year</td>
     <td>$date</td>
     <td>$event</td>";
     }
print "</table>";

> Excel has a feature called 'split' then 'freeze panes' that allows  a user to
>freeze column headers where showing a column title might be important  as the
>page is scrolled through.

>
> Is that possible to do with PHP?   I'm using the following in a query and
would
>really like to lock the header  titles where as the page is scrolled through,
>what that particular column is  indicating isn't lost, just as it wouldn't be
if
>a person were using the 'split'  feature in Excel.  Thanks very much for any
>help.   John

This is not strictly a PHP question since it relies on HTML, CSS and the ways
that browsers render each differently.  This sample mostly accomplishes what you
want in Firefox.  However, Safari does not respect the height and overflow
properties in the tbody element.  I don't have a way to check IE here nor
Chrome.  I also have not been able to get rid of the horizontal scroll bar in
this example but imagine some tinkering with the properties may allow this to be
accomplished.
_____

<div style="width:400px;">
<table border="1" style="width:100%">
   <thead>
     <tr>
       <th>Month</th>
       <th>Savings</th>
     </tr>
   </thead>
   <tfoot>
     <tr>
       <td>Sum</td>
       <td>$180</td>
     </tr>
   </tfoot>
   <tbody style="height:200px; overflow:scroll">
     <tr>
       <td>January</td>
       <td>$100</td>
     </tr>
     <tr>
       <td>February</td>
       <td>$80</td>
     </tr>
     <tr>
       <td>March</td>
       <td>$30</td>
     </tr>
     <tr>
       <td>April</td>
       <td>$120</td>
     </tr>
     <tr>
       <td>May</td>
       <td>$140</td>
     </tr>
     <tr>
       <td>Jun</td>
       <td>$280</td>
     </tr>
     <tr>
       <td>July</td>
       <td>$320</td>
     </tr>
     <tr>
       <td>August</td>
       <td>$275</td>
     </tr>
     <tr>
       <td>September</td>
       <td>$90</td>
     </tr>
     <tr>
       <td>October</td>
       <td>$110</td>
     </tr>
     <tr>
       <td>November</td>
       <td>$130</td>
     </tr>
     <tr>
       <td>December</td>
       <td>$200</td>
     </tr>
   </tbody>
  </table>
</div>
_____

Since this is handled irregularly by the browsers, it may be necessary to
display two tables, one that shows your column headings with appropriate widths
for each column per your desire, and a second table inside a <div> with a
specified width, height, and overflow:scroll properties.  On the second table
you'd have to again specify the column widths at least on the first row of data.

For refinement you might look at a CSS group that can help you refine this.  Of
course many in the CSS community advocate the elimination of tables altogether
in favor of placing each bit of content in <div> tags which are styled.
However, even this is not always consistent across browsers and you may be in
for surprises, as usual.

James Keeline

Hello,
 
Is there a way of selecting all columns *except* a named column?
If not, can one select columns using an asterisk *.  For example, Select all
columns whose names begin with 'p'.
 
I couldn't find it in the manual:
http://dev.mysql.com/doc/refman/5.0/en/select.html

The reson for wanting this is that I am using JOIN to join 10 tables or so.
 
Like this:
SELECT general. * , cv_courses. * , cv_jobs. * , cv_memberships. * , cv_prizes.*
etc...

The result obviously includes the ID column of each table.

I was just wondeing if there was a way of excluding those columns!

David




[Non-text portions of this message have been removed]

On Mon, Jan 3, 2011 at 12:42 PM, David Halliday <tetrahall@...> wrote:
> Hello,
>
> Is there a way of selecting all columns *except* a named column?
> If not, can one select columns using an asterisk *.  For example, Select all
columns whose names begin with 'p'.

> The reson for wanting this is that I am using JOIN to join 10 tables or so.
> The result obviously includes the ID column of each table.

How much of a lag / processing time will it be if you just selected
it, & did nothing to the ID field information ? - fractions of a
second ?

Would it take more processing time to select all the fields except the
ID field/column ?

I guess you'll need the ID field at some point, to match each table
with the data/record in the other tables...

(just curious)

--
G
Auckland Freecycle - New Zealand
http://nz.freecycle.org/

> Is there a way of selecting all columns *except* a named  column?

> If not, can one select columns using an asterisk *.  For example,  Select all
>columns whose names begin with 'p'.
>
> I couldn't find it in the  manual:
> http://dev.mysql.com/doc/refman/5.0/en/select.html
>
> The  reson for wanting this is that I am using JOIN to join 10 tables or  so.
>
> Like this:
> SELECT general. * , cv_courses. * , cv_jobs. * ,  cv_memberships. * ,
>cv_prizes.* etc...
>
> The result obviously includes the  ID column of each table.
>
> I was just wondeing if there was a way of  excluding those columns!
>
> David

I don't think so.  Of course, just because you select a column does not mean you
have to display it (or its name).  Perhaps you can grab the data and choose what
to display according to column names, etc.?

Possibly the columns with the same name ('id') are overlapping from a value
perspective?

A 10-table join has a strong chance of being slow.  Are you sure that this is
the best architecture and query design?  Do you need to carry around all of that
data at once?  Is there an option to use temporary tables with the data you
need?

James

--- On Sun, 2/1/11, James Keeline <keeline@...> wrote:


From: James Keeline <keeline@...>
Subject: Re: [php-list] MYSQL Select Syntax (select all except ...)
To: php-list@yahoogroups.com
Date: Sunday, 2 January, 2011, 23:50


> Is there a way of selecting all columns *except* a named  column?

> If not, can one select columns using an asterisk *.  For example,  Select all
>columns whose names begin with 'p'.
> 
> I couldn't find it in the  manual:
> http://dev.mysql.com/doc/refman/5.0/en/select.html
>
> The  reson for wanting this is that I am using JOIN to join 10 tables or  so.
> 
> Like this:
> SELECT general. * , cv_courses. * , cv_jobs. * ,  cv_memberships. * ,
>cv_prizes.* etc...
>
> The result obviously includes the  ID column of each table.
>
> I was just wondeing if there was a way of  excluding those columns!
>
> David


I don't think so.  Of course, just because you select a column does not mean you
have to display it (or its name).  Perhaps you can grab the data and choose what
to display according to column names, etc.?

Possibly the columns with the same name ('id') are overlapping from a value
perspective?

A 10-table join has a strong chance of being slow.  Are you sure that this is
the best architecture and query design?  Do you need to carry around all of that
data at once?  Is there an option to use temporary tables with the data you
need?

James

------------------------------------
 
Hello James,
 
Thank you for your response.
 
I am heeding your advice -:)
 
Almost a year ago I asked a question about storing Curriculum Vitae in mysql. 
 
You advised me to have several tables. See this post:-
http://tech.groups.yahoo.com/group/php-list/message/25195

Also, the overall advice (see the bottom of that post) was to use JOIN
 
According to MYSQL manual, te maximum number of JOIN tables that can be
rereferencedin a query is 61.
http://dev.mysql.com/doc/refman/5.0/en/joins-limits.html
 
I need all the data to display the cv of each applicant.  PHP script can Of
course exclude ID's  but I was just wondering if there is a way in the SELECT
Syntax.
 
What are your thoughts on this? 
 
Best regards
 
 
David
 
------------------------------------




[Non-text portions of this message have been removed]

On Jan 2, 2011, at 4:07 PM, David Halliday wrote:

>
> Almost a year ago I asked a question about storing Curriculum Vitae in mysql.
>
> You advised me to have several tables. See this post:-
> http://tech.groups.yahoo.com/group/php-list/message/25195
>
> Also, the overall advice (see the bottom of that post) was to use JOIN
>
> According to MYSQL manual, te maximum number of JOIN tables that can be
> rereferencedin a query is 61.
> http://dev.mysql.com/doc/refman/5.0/en/joins-limits.html
>
> I need all the data to display the cv of each applicant.  PHP script can Of
course exclude ID's  but I was just wondering if there is a way in the SELECT
Syntax.
>
> What are your thoughts on this?
>
You can't exclude the column from your query, no, but like they said, you don't
really have to, you should however write the query to select only the specifics
that you want if you just write 'select *' you will end up with a very
convoluted query with extra information.

If you're integrating it with PHP - why do you have to write it as one query,
why not query each table when you need it in the laying out process?




[Non-text portions of this message have been removed]

--- On Sun, 2/1/11, Gordon Stewart <gordonisnz@...> wrote:


From: Gordon Stewart <gordonisnz@...>
Subject: Re: [php-list] MYSQL Select Syntax (select all except ...)
To: php-list@yahoogroups.com
Date: Sunday, 2 January, 2011, 23:50


On Mon, Jan 3, 2011 at 12:42 PM, David Halliday <tetrahall@...> wrote:
> Hello,
>
> Is there a way of selecting all columns *except* a named column?
> If not, can one select columns using an asterisk *.  For example, Select all
columns whose names begin with 'p'.

> The reson for wanting this is that I am using JOIN to join 10 tables or so.
> The result obviously includes the ID column of each table.

How much of a lag / processing time will it be if you just selected
it, & did nothing to the ID field information ? - fractions of a
second ?

Would it take more processing time to select all the fields except the
ID field/column ?

I guess you'll need the ID field at some point, to match each table
with the data/record in the other tables...

(just curious)

-----------------------------------
 
Thank you, Gordon, for the rersponse. 

The ID field is actually used in the WHERE clause.

Yes, it will almost certianly be 'fractions of a second' but I was just
wondering, a purely "academic curiosity."
 
Best regards,
 
David
-----------------------------------





[Non-text portions of this message have been removed]

--- On Mon, 3/1/11, John Magee <jemagee@...> wrote:


From: John Magee <jemagee@...>
Subject: Re: [php-list] MYSQL Select Syntax (select all except ...)
To: php-list@yahoogroups.com
Date: Monday, 3 January, 2011, 0:12


On Jan 2, 2011, at 4:07 PM, David Halliday wrote:

> 
> Almost a year ago I asked a question about storing Curriculum Vitae in mysql. 
> 
> You advised me to have several tables. See this post:-
> http://tech.groups.yahoo.com/group/php-list/message/25195
>
> Also, the overall advice (see the bottom of that post) was to use JOIN
> 
> According to MYSQL manual, te maximum number of JOIN tables that can be
> rereferencedin a query is 61.
> http://dev.mysql.com/doc/refman/5.0/en/joins-limits.html
> 
> I need all the data to display the cv of each applicant.  PHP script can Of
course exclude ID's  but I was just wondering if there is a way in the SELECT
Syntax.
> 
> What are your thoughts on this? 
> 
------------------------------------------------

You can't exclude the column from your query, no, but like they said, you don't
really have to, you should however write the query to select only the specifics
that you want if you just write 'select *' you will end up with a very
convoluted query with extra information.

If you're integrating it with PHP - why do you have to write it as one query,
why not query each table when you need it in the laying out process?

------------------------------------------------
Thank you, John, for your response.

You said:
" ............. you should however write the query to select only the specifics
that you want if you just write 'select *' you will end up with a very
convoluted query with extra information"
 
But in practice one needs all the information in the row to process via php and
then dsplay.  For example, the past 10 jobs, employers, durations, dates, etc.
And the most important 10 courses they attended.  Other details are also needed
in the CV, e.g. professional memberships, etc.
 
The end result would be one page with a summary of the above details (and others
as well).
 
The second point:
".... If you're integrating it with PHP - why do you have to write it as one
query, why not query each table when you need it in the laying out process?"
 
I may be wrong, but my understanding of how mysql connections work, it is
advisable to pull the data in one go with one connection.  I have always been
told that it is faster and less of a strain on the resources to make a db
connection, pull the information that one requires in one go,  and close it.
 
The alternative, as I see it, is to connect then pull one chunk of info, then
reconnect again and so on.  Is this better?
 
Best regards,
 
David
 




[Non-text portions of this message have been removed]

On Jan 2, 2011, at 5:22 PM, David Halliday wrote:

>
> I may be wrong, but my understanding of how mysql connections work, it is
advisable to pull the data in one go with one connection.  I have always been
told that it is faster and less of a strain on the resources to make a db
connection, pull the information that one requires in one go,  and close it.
Yes you should pull it in one connection, but you dont have to pull it all in
one query

You're got multiple sections populated it seems, pull each section as a separate
query variable to group it all together if you are concerned about having the
master query

--- On Mon, 3/1/11, John Magee <jemagee@...> wrote:


From: John Magee <jemagee@...>
Subject: Re: [php-list] MYSQL Select Syntax (select all except ...)
To: php-list@yahoogroups.com
Date: Monday, 3 January, 2011, 1:24



On Jan 2, 2011, at 5:22 PM, David Halliday wrote:

> 
> I may be wrong, but my understanding of how mysql connections work, it is
advisable to pull the data in one go with one connection.  I have always been
told that it is faster and less of a strain on the resources to make a db
connection, pull the information that one requires in one go,  and close it.

Yes you should pull it in one connection, but you dont have to pull it all in
one query

You're got multiple sections populated it seems, pull each section as a separate
query variable to group it all together if you are concerned about having the
master query

------------------------------------
 
Okay, I understand the point now.  I will definitely try that.
Thanks a lot,
David
 




[Non-text portions of this message have been removed]

Hi David,

>Is there a way of selecting all columns *except* a named column?
That will be difficult

>If not, can one select columns using an asterisk *.  For example,
>Select all columns whose names begin with 'p'.
Yes, but...

>The reson for wanting this is that I am using JOIN to join 10 tables or so.
> 
>Like this:
>SELECT general. * , cv_courses. * , cv_jobs. * , cv_memberships. *
>, cv_prizes.* etc...

Do NOT put a spave between the dot and the asterisk. So:
SELECT general.* , cv_courses.* , cv_jobs.* , cv_memberships.*, cv_prizes.*
etc...

>The result obviously includes the ID column of each table.
>
>I was just wondeing if there was a way of excluding those columns!
You might simply only select the columns you want. Even when using the ID
columns to perform the join, there is no need to include them. The MySQL
engine probably first performs the join and then decides which fields to
include. Limiting the query to only the fields that you want to use has two
advantages:
- less memory use by MySQL
- probably faster displaying of results as you will not need to filter out
part of the resulting fields anymore.

>A 10-table join has a strong chance of being slow.
Very true. But on the other hand, for flexibility, data structure
normalization, and for avoiding data duplicity in the database it is better
not to group data. And thus create as many tables as necessary to maintain
the data normalized. You WILL need to index secondary keys for speed.

> Is there an option to use temporary tables with the data you need?
That indeed would be the best solution, depending on the situation. Joining
many tables that only contain numeric values usually is not that slow.

Marc

Hi David,

>Is there a way of selecting all columns *except* a named column?
That will be difficult

>If not, can one select columns using an asterisk *.  For example,
>Select all columns whose names begin with 'p'.
Yes, but...

>The reson for wanting this is that I am using JOIN to join 10 tables or so.
> 
>Like this:
>SELECT general. * , cv_courses. * , cv_jobs. * , cv_memberships. *
>, cv_prizes.* etc...

Do NOT put a spave between the dot and the asterisk. So:
SELECT general.* , cv_courses.* , cv_jobs.* , cv_memberships.*, cv_prizes.*
etc...

>The result obviously includes the ID column of each table.
>
>I was just wondeing if there was a way of excluding those columns!
You might simply only select the columns you want. Even when using the ID
columns to perform the join, there is no need to include them. The MySQL
engine probably first performs the join and then decides which fields to
include. Limiting the query to only the fields that you want to use has two
advantages:
- less memory use by MySQL
- probably faster displaying of results as you will not need to filter out
part of the resulting fields anymore.

>A 10-table join has a strong chance of being slow.
Very true. But on the other hand, for flexibility, data structure
normalization, and for avoiding data duplicity in the database it is better
not to group data. And thus create as many tables as necessary to maintain
the data normalized. You WILL need to index secondary keys for speed.

> Is there an option to use temporary tables with the data you need?
That indeed would be the best solution, depending on the situation. Joining
many tables that only contain numeric values usually is not that slow.

Marc
-------------------------------------
 
Hello Marc,
All the points have been noted. Thank you. There are 2 issues:-

JOIN OR NO JOIN?
Having several tables instead of one large table seems to be accepted generally
as the best practice. 
 
But why join at all if one cannot join multiple tables. If one is to have sevral
queries, why not forget JOIN altogether.  Just open a connection, query the 10
tables *sepearately* and return the BIG array to be processed by the php script.

I wonder if that is not the better approach in this case.
 
Temporary Tables
Is this really the best solution here?  I honestly cannot see what is being
achieved. 

A temporary table has to be populated by data collected from several other
tables (using JOIN or otherwise).  Then, the data is selected again from the
temporary table and returnd to the script!  
 
May be there is something I am missing here.  But I fail to see the point.
 
Any thoughts on this?
 
David
 
 
 




[Non-text portions of this message have been removed]

On Jan 3, 2011, at 3:31 PM, David Halliday wrote:

>
> Hello Marc,
> All the points have been noted. Thank you. There are 2 issues:-
>
> JOIN OR NO JOIN?
> Having several tables instead of one large table seems to be
> accepted generally as the best practice.
>
> But why join at all if one cannot join multiple tables. If one is to
> have sevral queries, why not forget JOIN altogether.  Just open a
> connection, query the 10 tables *sepearately* and return the BIG
> array to be processed by the php script.
>
> I wonder if that is not the better approach in this case.
>
> Temporary Tables
> Is this really the best solution here?  I honestly cannot see what
> is being achieved.
>
> A temporary table has to be populated by data collected from several
> other tables (using JOIN or otherwise).  Then, the data is selected
> again from the temporary table and returnd to the script!
>
> May be there is something I am missing here.  But I fail to see the
> point.
>
> Any thoughts on this?
>
> David
>
>
Temporary tables I don't see the use for in something like this, i
have some very big datawarehouses on my MySQL at home (Pitch fx data
for the past 3 seasons, NBA box score data for the past four, in store
whole foods sales for 35 different skus over the past 3 years) and
temporary tables can sometimes come in handy when I want to run
complicated queries requiring filtering out based on an aggregate
function (for instance plate appearances).  It's not  atable I always
need but it's useful to have.

Though when I have things like that, I usually create views as they
work better.

Hi David,

>JOIN OR NO JOIN?
>Having several tables instead of one large table seems to be
>accepted generally as the best practice. 
> 
>But why join at all if one cannot join multiple tables. If one is
>to have sevral queries, why not forget JOIN altogether.  Just open
>a connection, query the 10 tables *sepearately* and return the BIG
>array to be processed by the php script.
>
>I wonder if that is not the better approach in this case.
It is not. PHP is an interpreted language, unlike the MySQL engine (which is
compiled). If you write code in PHP it will inevitably execute the "join"
slower than the MySQL engine would.

First you would select all records from one table.
Then you would loop through all selected records, selecting all matching
records from another table.
Then for all those records selecting some records from a third table, and so
on.
And then in the end you still need to remove the unwanted results...

The result would be very slow, because of the repeated looping involved
(much slower than having MySQL do the job).

Maybe you experience some performance problems with joining lots of tables,
but for sure it is still better to JOIN than to have PHP code assemble the
dataset you want to display. For the join to work best (fastest) the fields
to join on should preferably be primary or secondary keys. A primary key is
indexed by definition. But you will find the join will be considerably
faster if the other, secondary key, fields are indexed fields as well.
Creating indexes on fields is optional when creating a table, except for the
primary key, so remember which fields may serve as secondary keys and create
the index on these fields manually.

The only situation in which you might want to avoid joining by creating one
table only is when data are never updated/changed/deleted. The single table
approach would result in a big table (many rows) with a lot of data
duplicity. A nightmare when needing to update data, but easier (joinless)
for selecting rows. And even then, as filtering in joins is easier, I would
think twice before putting everything in one table. Data are not to be
duplicated in a database, as a rule of thumb.

Marc

> >JOIN OR NO JOIN?

> >Having several tables instead of  one large table seems to be
> >accepted generally as the best  practice.
> >
> >But why join at all if one cannot join multiple  tables. If one is
> >to have sevral queries, why not forget JOIN  altogether.  Just open
> >a connection, query the 10 tables *sepearately*  and return the BIG
> >array to be processed by the php  script.

We have been speaking in generalities and a lot of this depends on the specifics
of your table structure.  The decision about when to make another table usually
comes down to rules of normalization
(http://en.wikipedia.org/wiki/Database_normalization).


There are several of these but the principal one concerns data that is
repeated.  For example, let's say that you have a bunch of people but they all
work in some smaller number of locations.  It is often well to create a table
for the locations (with related data for each location) and use an integer in
each person record to relate to the more detailed information in the locations
table.

As Marc pointed out, if you are making joins with integer values then you should
get pretty quick results from MySQL.  When the joins are based on strings,
especially on unindexed columns, that can be a bottleneck.

Let's say in the example of locations that you only have 10 or 50 of them.  Is
it better to load those once in a PHP array and display the relevant values as
needed or to use a join on that table and pull over the needed values for some
arbitrarily large number of rows, say 1,000?  In those cases, you probably want
to create a separate test script that can time the difference between the two.
However, watch out for query caching which can affect the results.

There may be times, however, where adding another table for repeated data may be
counterproductive.  Imagine having a separate table for last names because some
names are common.  If disk space were at a premium, this might be necessary.
However, the overhead of having one more join for this kind of information might
not offset the space savings.  Also, many-table structures can be more difficult
to program from the standpoint of INSERTing data or making an UPDATE.  The table
structure and relationships need to be immediately understandable to you and
decipherable to the next person who is to work on the project.  When it is not,
there is a question about whether the tools are being used to their best
advantage.  When things get complex, be sure to document them.  This is both for
your own memory 6 or 12 months down the road as well as for the next person to
work on it.  That includes assistants or consultants as well as the possibility
of your replacement.

Getting back to your data.  You mentioned that you have 10 tables with cv
information and that we looked at this as a group a year ago.  Probably we all
need a reminder of just what is stored in each of these tables (i.e. the table
structure) and what sort of joins you are doing.

James Keeline

Hi Everyone

I want to make inventory control system like goods receiving from different
parties  can anyone tell me the table structure in mysql plz add me in facebook
via my email addres




[Non-text portions of this message have been removed]

On Jan 4, 2011, at 12:41 PM, FARHA wrote:

> Hi Everyone
>
> I want to make inventory control system like goods receiving from
> different parties  can anyone tell me the table structure in mysql
> plz add me in facebook via my email addres
>
>
>
>
> [Non-text portions of this message have been removed]
>
Are you willing to pay someone to do your work for you?

On Tue, Jan 4, 2011 at 14:50, John Magee <jemagee@...> wrote:

>
>
>
> On Jan 4, 2011, at 12:41 PM, FARHA wrote:
>
> > Hi Everyone
> >
> > I want to make inventory control system like goods receiving from
> > different parties can anyone tell me the table structure in mysql
> > plz add me in facebook via my email addres
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> Are you willing to pay someone to do your work for you?
>
>
>


Farha,

1) No one is going to email outside of this group.
2) If you actually want help you'll probably want to put a real subject in
your email.
3) How about telling us what type of structure you have already and someone
may provide you with assistance.

Wade
--
---
Registered Linux User: #480675
Linux since June 2005


[Non-text portions of this message have been removed]

>JOIN OR NO JOIN?
>Having several tables instead of one large table seems to be
>accepted generally as the best practice. 
> 
>But why join at all if one cannot join multiple tables. If one is
>to have sevral queries, why not forget JOIN altogether.  Just open
>a connection, query the 10 tables *sepearately* and return the BIG
>array to be processed by the php script.
>
>I wonder if that is not the better approach in this case.
It is not. PHP is an interpreted language, unlike the MySQL engine (which is
compiled). If you write code in PHP it will inevitably execute the "join"
slower than the MySQL engine would.

First you would select all records from one table.
Then you would loop through all selected records, selecting all matching
records from another table.
Then for all those records selecting some records from a third table, and so
on.
And then in the end you still need to remove the unwanted results...

The result would be very slow, because of the repeated looping involved
(much slower than having MySQL do the job).

Maybe you experience some performance problems with joining lots of tables,
but for sure it is still better to JOIN than to have PHP code assemble the
dataset you want to display. For the join to work best (fastest) the fields
to join on should preferably be primary or secondary keys. A primary key is
indexed by definition. But you will find the join will be considerably
faster if the other, secondary key, fields are indexed fields as well.
Creating indexes on fields is optional when creating a table, except for the
primary key, so remember which fields may serve as secondary keys and create
the index on these fields manually.

The only situation in which you might want to avoid joining by creating one
table only is when data are never updated/changed/deleted. The single table
approach would result in a big table (many rows) with a lot of data
duplicity. A nightmare when needing to update data, but easier (joinless)
for selecting rows. And even then, as filtering in joins is easier, I would
think twice before putting everything in one table. Data are not to be
duplicated in a database, as a rule of thumb.

Marc
------------------------------------
Hello Marc,
 
Many thanks for the above information.  Just one point:
 
You said:
"First you would select all records from one table.
Then you would loop through all selected records, selecting all matching records
from another table.
Then for all those records selecting some records from a third table, and so
on.
And then in the end you still need to remove the unwanted results..."
--
No!  Looping (as described above) is not really needed in this case.. 
All the records pertaining to a certain UserID are selected from every table, as
an array.
Then all the arrays are retuned to the PHP script as a big array.
 
As I understand it, it is MYSQL that is actually doing the 'selecting' job.  PHP
then processes the retrieved data.
 
Even if  JOIN is used, PHP would still be doing the same processing once the
records are retrieved as an array.
 
David
 
 




[Non-text portions of this message have been removed]

> >JOIN OR NO JOIN?

> >Having several tables instead of  one large table seems to be
> >accepted generally as the best  practice.
> >
> >But why join at all if one cannot join multiple  tables. If one is
> >to have sevral queries, why not forget JOIN  altogether.  Just open
> >a connection, query the 10 tables *sepearately*  and return the BIG
> >array to be processed by the php  script.

We have been speaking in generalities and a lot of this depends on the specifics
of your table structure.  The decision about when to make another table usually
comes down to rules of normalization
(http://en.wikipedia.org/wiki/Database_normalization). 


There are several of these but the principal one concerns data that is
repeated.  For example, let's say that you have a bunch of people but they all
work in some smaller number of locations.  It is often well to create a table
for the locations (with related data for each location) and use an integer in
each person record to relate to the more detailed information in the locations
table.

As Marc pointed out, if you are making joins with integer values then you should
get pretty quick results from MySQL.  When the joins are based on strings,
especially on unindexed columns, that can be a bottleneck.

Let's say in the example of locations that you only have 10 or 50 of them.  Is
it better to load those once in a PHP array and display the relevant values as
needed or to use a join on that table and pull over the needed values for some
arbitrarily large number of rows, say 1,000?  In those cases, you probably want
to create a separate test script that can time the difference between the two. 
However, watch out for query caching which can affect the results.

There may be times, however, where adding another table for repeated data may be
counterproductive.  Imagine having a separate table for last names because some
names are common.  If disk space were at a premium, this might be necessary. 
However, the overhead of having one more join for this kind of information might
not offset the space savings.  Also, many-table structures can be more difficult
to program from the standpoint of INSERTing data or making an UPDATE.  The table
structure and relationships need to be immediately understandable to you and
decipherable to the next person who is to work on the project.  When it is not,
there is a question about whether the tools are being used to their best
advantage.  When things get complex, be sure to document them.  This is both for
your own memory 6 or 12 months down the road as well as for the next person to
work on it.  That includes assistants or consultants as well as the possibility
of your replacement.

Getting back to your data.  You mentioned that you have 10 tables with cv
information and that we looked at this as a group a year ago.  Probably we all
need a reminder of just what is stored in each of these tables (i.e. the table
structure) and what sort of joins you are doing.

James Keeline
------------------------------------
 
Hello,
The above information and the provided link are very useful, thank you.

Okay, it is simply like this:
Each job applicants fills a big form and the data is stored in mysql.
Then each applicant's cv is displayed on a single page.
 
The required data include qualifications past employements, certificates,
prizes, professional memberships, spoken languages, etc.
 
They are allowed u to 10 items of each category mentioned above.
 
Now I was advised that it is best to have separate tables for each category.
 
To display the cv, the db is queried and ALL the data there for a particular
user must be retrieved (including the empty ones, some have done only 2 courses,
or have never worked etc..)
 
Most these categories require TEXT data not integers. The question is: if JOIN
is going to be slow, why not just query each table with a "WHERE  UserID =xx"
and return all the data as a big array.
 
It looks to me now that this is the better approach, but I may be wrong.
 
David




[Non-text portions of this message have been removed]

Hi there

if you want to get everything in one query you might force mysql do use
disc space for temporary tables. That's getting freaking slow. The
memory mysql is allowed to use is limited, just try to stay in memory.

If you exceed this limit I learned it is better to only join the tables
having information I need for the WHERE statement and to get all foreign
keys needed to display the result. This reduces the mysql memory usage
and speeds up. Then I have some php functions like
display_company_by_id($record.company_id) which queries mysql only when
the ID is unknown - an remembers it in an array for caching. If another
row has the same company the cache is used.

MySql is fast on simple queries and using a simple caching like
$GLOBALS["mysql-cache"]["companies"][$company["id"]]=$company can be the
fastest solution. Of course you could also go for memcached so the cache
can be used for multiple php-script calls.

HTH, Patrick

Am 03.01.2011 02:28, schrieb David Halliday:
>
>
>
> --- On Mon, 3/1/11, John Magee <jemagee@...
> <mailto:jemagee%40cox.net>> wrote:
>
> From: John Magee <jemagee@... <mailto:jemagee%40cox.net>>
> Subject: Re: [php-list] MYSQL Select Syntax (select all except ...)
> To: php-list@yahoogroups.com <mailto:php-list%40yahoogroups.com>
> Date: Monday, 3 January, 2011, 1:24
>
> On Jan 2, 2011, at 5:22 PM, David Halliday wrote:
>
> >
> > I may be wrong, but my understanding of how mysql connections work,
> it is advisable to pull the data in one go with one connection.  I
> have always been told that it is faster and less of a strain on the
> resources to make a db connection, pull the information that one
> requires in one go,  and close it.
>
> Yes you should pull it in one connection, but you dont have to pull it
> all in one query
>
> You're got multiple sections populated it seems, pull each section as
> a separate query variable to group it all together if you are
> concerned about having the master query
>
> ------------------------------------
>
> Okay, I understand the point now.  I will definitely try that.
> Thanks a lot,
> David
>
>
> [Non-text portions of this message have been removed]
>
>



[Non-text portions of this message have been removed]

Since taking over management of a site a few years back, I've slowly been
adding a custom CMS system. At first, the cms was located in a folder within
the public_html folder.

I recently moved the cms to a secure folder, with an https address.
Since then, the upload image forms no longer work. I've changed the path to the
upload folder to a full url path pointing to the http location without success.

Any ideas on a workaround, other than moving the cms back into the html
folder?

php on an apache server

thanks,
david

Since taking over management of a site a few years back, I've slowly been
adding a custom CMS system. At first, the cms was located in a folder within
the public_html folder.

I recently moved the cms to a secure folder, with an https address.
Since then, the upload image forms no longer work. I've changed the path to the
upload folder to a full url path pointing to the http location without success.

Any ideas on a workaround, other than moving the cms back into the html
folder?

php on an apache server

thanks,
david
_____

I don't think anyone can help you much here with the level of detail you have
provided.  I'll make a couple guesses but they could be wrong since you have a
custom CMS and have given us no code.

A typical file upload form starts like this:

<form action="upload.php" enctype="multipart/form-data" method="post">

I have used "upload.php" to represent the name of the script that receives the
form data and processes the uploaded file.  When just a filename is given like
this, the same protocol used to display the page (http or https) will be used
for the upload.  However, if it must be handled through https because of
.htaccess or other Apache directives, you may have to give a fully-qualified URL
with the https to point to the upload script.

James

[Non-text portions of this message have been removed]

On Tue, Jan 18, 2011 at 9:24 AM, david <dadoonan@...> wrote:
> Since taking over management of a site a few years back, I've slowly been
> adding a custom CMS system. At first, the cms was located in a folder within
> the public_html folder.
>
> I recently moved the cms to a secure folder, with an https address.
> Since then, the upload image forms no longer work. I've changed the path to
the upload folder to a full url path pointing to the http location without
success.
>
> Any ideas on a workaround, other than moving the cms back into the html
> folder?
>
> php on an apache server
>
> thanks,
> david
>
> ------------------------------------
>
> Please remember to write your response BELOW this line or you will be RETURNED
TO MODERATED STATUS.
>
Hi David,

When you say "the upload image forms no longer work", what are the
errors?  (Be specific.)

Thanks, Jenny
--
~~~~~~~~~~~~~~~~~~~~
Jenny Connors
WhiteWaveDesigns.com
215 Essex Avenue
Gloucester MA 01930
h: 978-281-0472
c: 978-290-3968
f: 225-410-4037

----- Original Message -----
From: "david"

Since taking over management of a site a few years back, I've slowly been
adding a custom CMS system. At first, the cms was located in a folder within
the public_html folder.

I recently moved the cms to a secure folder, with an https address.
Since then, the upload image forms no longer work. I've changed the path to
the upload folder to a full url path pointing to the http location without
success.

Any ideas on a workaround, other than moving the cms back into the html
folder?

php on an apache server

thanks,
david

------------------------------------


There is not enough details here.

You said - "I recently moved the cms to a secure folder" so I am guessing
that you are using either <VirtualHost *:443> in httpd.conf or RewriteCond
%{HTTPS}=on in a .htaccess or even RewriteRule ^/?secure/(.*)$
/new/path/to/$1?%{QUERY_STRING}

These work differently and may impact on file uploads to your temp upload
directory.

It could also be a file size issue, do small file work but not larger?

Hello,

I'm using the following query to query info from a database by year or piece of
equipment.  The specific equipment is listed on a pull down menu as is the past
couple of years and presets are 'All' to show everything in the table.

My question, how might I use 'ORDER BY' with the code below?  I'm trying to get
the query readout somewhat chronological and could possibly use id but had
planned on using 'year' (calendar year) to keep the query readout nice and
orderly looking.  When I placed it after "WHERE 1 = 1 "; it read great with the
preset of 'All' but didn't read properly if I clicked back in the browser and
then went after a specific piece of equipment or different year (something other
than All).

Thanks for any help or ideas.
john


$query = "SELECT * FROM $table WHERE 1 = 1 ";
          if($equip != "All") $query .= "and equip = '".$equip."'";
          if($year != "All") $query .= "and year = '".$year."'";
$result = mysql_query($query);

I'm using the following query to query info from a database by year or piece of
equipment.  The specific equipment is listed on a pull down menu as is the past
couple of years and presets are 'All' to show everything in the table.


My question, how might I use 'ORDER BY' with the code below?  I'm trying to get
the query readout somewhat chronological and could possibly use id but had
planned on using 'year' (calendar year) to keep the query readout nice and
orderly looking.  When I placed it after "WHERE 1 = 1 "; it read great with the
preset of 'All' but didn't read properly if I clicked back in the browser and
then went after a specific piece of equipment or different year (something other
than All).

Thanks for any help or ideas.
john


$query = "SELECT * FROM $table WHERE 1 = 1 ";
          if($equip != "All") $query .= "and equip = '".$equip."'";
          if($year != "All") $query .= "and year = '".$year."'";
$result = mysql_query($query);
_____

Since the ORDER BY clause has to follow the complete WHERE clause, you will need
to add it after your second IF statement like this:

$query  = "SELECT * FROM $table WHERE 1 = 1 ";
if($equip != "All") $query .= "and equip = '$equip' ";
if($year  != "All") $query .= "and year  = '$year' ";
$query .= " ORDER BY year";
$result = mysql_query($query);

Be sure that there is at least one space between each clause.  During
development you may want to use a diagnostic statements like this:

$result = mysql_query($query) or die(mysql_error() . "<hr />$query");

James

[Non-text portions of this message have been removed]

Hello,

I need an image file manager, but not only an image manager, once I upload the
images, i want to do this directly from a php form, and retrieve the values of
the images uploaded/found on the gallery, and submit them along with other form
fields to mysql database

I do not want to use Tiny MCE, which seems to have something not working with
the servers we use online
Many Thanks

Thanks very much, James.  I'll give it a try.  Appreciate your help.

John

<snip>