[PHP-DB] Winamp-Like Search
Would it be possible to create a Winamp-like search in a web-based form (the search basically checks your list of music as you type and pulls all the matches it finds)? Say you pull the results of a SQL Query and assign them to a variable. Can you take user input and compare it to the variable for matches before the user ever even submits the form? Thanks, Nikhil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Having more than one id in the same field
Hi. I have a database designing question to ask. I want to build a table of events. Among the other fields there must be a field that holds the 'responsible organization' of the event. This organization of course will be responsible for other events as well so I have to create another table that holds the organizations (id, name, phones, director etc) and then just pull the organization id to the events table. The problem is that it happens too often to have 2 organizations responsible for the same event so I'll have to add them both to the events table in the same record. How do you advice me to do that? I thought that I could use a text field to hold the ids and then when searching the database just change the MySQL command from ...where events.id='$id'... (As it would be if only one id was going to be used) to ...where '$id' in (events.ids)... or maybe something using LIKE. Do you think it can be done this way? Apart from the responsible organization I may have other fields in the same table having the same problem (for example: the event visitors are staying in one hotel and I want to hold control of the hotels as well. Maybe 2 hotels are used instead of one). If I solve my problem this way, do you think that it will be too difficult or 'heavy' to have more than one condition like this in my queries? Do you think of any other way? Thanx in advance Achilles -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Extracting column names from a db
I need to write a script that will extract the names of the columns in my database eg. user_id, username can anyone help as to how to do this! I have tried ** *** mysql_select_db(filterseveuk) or die(mysql_error()); $query = SHOW COLUMNS FROM .$table. ; $result = mysql_query ( $query ) or die( mysql_error () ); $numrows = mysql_num_rows ($result); $row = mysql_fetch_array ($result); for($x=0; $x = $numrows; $x++){ echo $row[$x] ; } ** *** this doesn't work the way i want it to and gives me the output ** *** user_idint(11)PRIauto_increment Warning: Undefined offset: 6 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 7 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 8 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 9 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 10 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 11 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 12 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 13 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 14 in /home/filterseveuk/public_html/admin/index.php on line 30 _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Extracting column names from a db
A mix-up in your loop. Try: while ( $row = mysql_fetch_array( $result ) ) { echo $row['Field'] ; } (you may just as well use the field names provided by SHOW COLUMNS - more readable than numerical indexes) HTH Ignatius - Original Message - From: David Rice [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, November 10, 2002 4:18 PM Subject: [PHP-DB] Extracting column names from a db I need to write a script that will extract the names of the columns in my database eg. user_id, username can anyone help as to how to do this! I have tried ** *** mysql_select_db(filterseveuk) or die(mysql_error()); $query = SHOW COLUMNS FROM .$table. ; $result = mysql_query ( $query ) or die( mysql_error () ); $numrows = mysql_num_rows ($result); $row = mysql_fetch_array ($result); for($x=0; $x = $numrows; $x++){ echo $row[$x] ; } ** *** this doesn't work the way i want it to and gives me the output ** *** user_idint(11)PRIauto_increment Warning: Undefined offset: 6 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 7 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 8 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 9 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 10 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 11 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 12 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 13 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 14 in /home/filterseveuk/public_html/admin/index.php on line 30 _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Extracting column names from a db
The problem is that you're fetching a single row and then print out the result, whereas Mysql returns a row for each field. Try something like this: mysql_select_db(filterseveuk) or die(mysql_error()); $query = SHOW COLUMNS FROM .$table. ; $result = mysql_query ( $query ) or die( mysql_error () ); while ($row = mysql_fetch_assoc ($result)) echo $row['Field']; This loops through each row in the result set and prints out the value of the field called Field, which actually contains the name of the field in the table. Cheers, Marco On Sun, 2002-11-10 at 10:18, David Rice wrote: I need to write a script that will extract the names of the columns in my database eg. user_id, username can anyone help as to how to do this! I have tried ** *** mysql_select_db(filterseveuk) or die(mysql_error()); $query = SHOW COLUMNS FROM .$table. ; $result = mysql_query ( $query ) or die( mysql_error () ); $numrows = mysql_num_rows ($result); $row = mysql_fetch_array ($result); for($x=0; $x = $numrows; $x++){ echo $row[$x] ; } ** *** this doesn't work the way i want it to and gives me the output ** *** user_idint(11)PRIauto_increment Warning: Undefined offset: 6 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 7 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 8 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 9 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 10 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 11 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 12 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 13 in /home/filterseveuk/public_html/admin/index.php on line 30 Warning: Undefined offset: 14 in /home/filterseveuk/public_html/admin/index.php on line 30 _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Performance for how many columns to update?
I'm wondering how significant the performance differences between: mysql_query(update table set col1='val1' where whatever='whatever'); and mysql_query(update table set col1='val1',col2='val2',col3='val3'... where whatever='whatever'); -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Calendar error - can anyone help
Dear all I am trying to create a basic calendar of events and all is working except for the following error: I have a mySQL database with the following fields: id - startdate - endate - title - event I am using the followuing command to select the dates that are booked and colour code the relevant cells of the calendar: /snip $sql_currentday = $year-$month-$zz; $eventQuery = SELECT title FROM prestigecal WHERE '$sql_currentday' BETWEEN startdate AND enddate;; $eventExec = mysql_query($eventQuery); while($row = mysql_fetch_array($eventExec)) { if (strlen($row[title]) 0) { echo td bgcolor=\red\ valign=\middle\ align=\center\font size=\1px\ face=\Verdana\$zz/font/td\n; $result_found = 1; This seems to work and populates the calendar with the correct colours but only if the dates have the fist figure the same e.g. start date = 2002-12-16 end date = 2002-12-19 will populate the cells correctly (i.e. 16 / 17 / 18 / 19) But if the dates are as follows: start date = 2002-12-16 end date = 2002-12-20 it populates the correct dates again (i.e. 16 / 17 / 18 / 19 / 20) but also colour codes the date 2002-12-20 for some reason It seems to think that the 2 (of the 20) is a separate date. This also applies when you go into other ranges e.g. 2002-12-25 to 2002-12-30 will populate correctly but also the 3rd can anyone see what is going on with the script and where I have gone wrong. If any further code of mine is required - just ask. I would like to get this finish and then post it to the forum in case anyone else would like it Thanks for your time Ray
Re: [PHP-DB] Winamp-Like Search
I'm sure that's possible with javascript and frames. There will be a lot of issues though, depending on the size of your MP3 list, with querying even an array or hash on every character a person types. Check out the javascript onchange='javascriptfunction();' in the input tag. Peter On Sat, 9 Nov 2002, Nikhil Prashar wrote: Would it be possible to create a Winamp-like search in a web-based form (the search basically checks your list of music as you type and pulls all the matches it finds)? Say you pull the results of a SQL Query and assign them to a variable. Can you take user input and compare it to the variable for matches before the user ever even submits the form? Thanks, Nikhil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Having more than one id in the same field
Add a third table: event table organization table responsibility table id eventID organizationID Now you can have more than one organization responsible for each event, and each organization can have one or more events. Just drop the organizationID from the event table, or leave it as the Primary Organization and everyone else is a sub organization... though that should probably be kept in the responsibility table, not the event table. Peter On Sun, 10 Nov 2002, Achilles Maroulis wrote: Hi. I have a database designing question to ask. I want to build a table of events. Among the other fields there must be a field that holds the 'responsible organization' of the event. This organization of course will be responsible for other events as well so I have to create another table that holds the organizations (id, name, phones, director etc) and then just pull the organization id to the events table. The problem is that it happens too often to have 2 organizations responsible for the same event so I'll have to add them both to the events table in the same record. How do you advice me to do that? I thought that I could use a text field to hold the ids and then when searching the database just change the MySQL command from ...where events.id='$id'... (As it would be if only one id was going to be used) to ...where '$id' in (events.ids)... or maybe something using LIKE. Do you think it can be done this way? Apart from the responsible organization I may have other fields in the same table having the same problem (for example: the event visitors are staying in one hotel and I want to hold control of the hotels as well. Maybe 2 hotels are used instead of one). If I solve my problem this way, do you think that it will be too difficult or 'heavy' to have more than one condition like this in my queries? Do you think of any other way? Thanx in advance Achilles -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] retrieving data from mysql table
Notice that you are adding $x in the loop. You are getting a row, printing out index 0 of table_headers, incrementing $x, then moving to the next row. Basically you are fetching row 1 column 1, row 2 column 2, row 3 column 3, etc. instead of fetching all rows. Do this instead: $query = SELECT * FROM .$table. ; $result = mysql_query ( $query ) or die( mysql_error () ); while ( $row = mysql_fetch_assoc ($result)) { while(list(,$col)=each($row)) { echo TD{$col}/TD; } } This will echo all rows, and for each row will echo all values in order of your table, which is also the order of $table_headers. Peter On Sun, 10 Nov 2002, David Rice wrote: ? /* Select all the records from the table */ $query = SELECT * FROM .$table. ; $result = mysql_query ( $query ) or die( mysql_error () ); $x = 0 ; while ( $row = mysql_fetch_assoc ($result)) { ? TD ? echo $row[$table_headers[$x]] ; ? /TD ? $x++ ; } ? /TABLE --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Performance for how many columns to update?
Straight from the MySQL Documentation, which is where you should look first always: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Update_speed 5.2.10 Speed of UPDATE Queries Update queries are optimised as a SELECT query with the additional overhead of a write. The speed of the write is dependent on the size of the data that is being updated and the number of indexes that are updated. Indexes that are not changed will not be updated. Also, another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table. Note that, with dynamic record format, updating a record to a longer total length may split the record. So if you do this often, it is very important to OPTIMIZE TABLE sometimes. See section 4.5.1 OPTIMIZE TABLE Syntax. If you aren't doing an update of 50K per row, updating 3 columns instead of 1 will be much quicker (quoted from the manual). If you are doing 10,000 updates, read more of the manual on the syntax of UPDATE because you can delay your updates which allows MYSQL to update the table at its leisure which offers better performance. Peter, who reminds you to always read the manual (or less kindly RTFM!). On Sun, 10 Nov 2002, Leif K-Brooks wrote: I'm wondering how significant the performance differences between: mysql_query(update table set col1='val1' where whatever='whatever'); and mysql_query(update table set col1='val1',col2='val2',col3='val3'... where whatever='whatever'); -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Performance for how many columns to update?
Yes, I know that one query is more efficient than 3, but I'm trying to do a mysql_fetch_array that automatically changes the row at the end of the script if it has changed. I'm trying to decide whether to make it only update the changed rows or be lazy and make it update them all if only 1 has changed. Peter Beckman wrote: Straight from the MySQL Documentation, which is where you should look first always: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Update_speed 5.2.10 Speed of UPDATE Queries Update queries are optimised as a SELECT query with the additional overhead of a write. The speed of the write is dependent on the size of the data that is being updated and the number of indexes that are updated. Indexes that are not changed will not be updated. Also, another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table. Note that, with dynamic record format, updating a record to a longer total length may split the record. So if you do this often, it is very important to OPTIMIZE TABLE sometimes. See section 4.5.1 OPTIMIZE TABLE Syntax. If you aren't doing an update of 50K per row, updating 3 columns instead of 1 will be much quicker (quoted from the manual). If you are doing 10,000 updates, read more of the manual on the syntax of UPDATE because you can delay your updates which allows MYSQL to update the table at its leisure which offers better performance. Peter, who reminds you to always read the manual (or less kindly RTFM!). On Sun, 10 Nov 2002, Leif K-Brooks wrote: I'm wondering how significant the performance differences between: mysql_query(update table set col1='val1' where whatever='whatever'); and mysql_query(update table set col1='val1',col2='val2',col3='val3'... where whatever='whatever'); -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law.
[PHP-DB] Optimizing
Hi there everyone, How does indexing work, and does it speed up small / average size DB's of around 20,000 records, with 12 columns per record? (Some columns being paragraphs of text). Also, do you create an index on everything or just 1 item or or or :-) I've not looked at indexing but think it's time to start, but in a way even I can understand so I thought i'd ask you all here :-) Thanks Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Optimizing
This varies from database to database. I would suggest reviewing the documentation on your database type. For review here is some information on indexing from PostgreSQL (my database of choice): http://www.postgresql.org/idocs/index.php?indexes.html That should give you some ideas on how indexing works. HTHs On Sun, 2002-11-10 at 18:46, Chris Payne wrote: Hi there everyone, How does indexing work, and does it speed up small / average size DB's of around 20,000 records, with 12 columns per record? (Some columns being paragraphs of text). Also, do you create an index on everything or just 1 item or or or :-) I've not looked at indexing but think it's time to start, but in a way even I can understand so I thought i'd ask you all here :-) Thanks Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- .: B i g D o g :. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Performance for how many columns to update?
Update them all. It is easier (and faster) to do that even if none of them has changed than it is to try and figure out which one changed. MySQL doesn't do anything if the row/column hasn't changed, so there is only the overhead of a connection, which is already there since you are doing the fetch/query. Being lazy actually has its benefits in this case. Doing the fetch and then testing if you should update is actually more taxing on the DB (and the server for that matter if we are talking in CPU cycles) than it would be to just do an update on all columns with a where clause on an indexed (or preferably primary keyed) column. Peter On Sun, 10 Nov 2002, Leif K-Brooks wrote: Yes, I know that one query is more efficient than 3, but I'm trying to do a mysql_fetch_array that automatically changes the row at the end of the script if it has changed. I'm trying to decide whether to make it only update the changed rows or be lazy and make it update them all if only 1 has changed. Peter Beckman wrote: Straight from the MySQL Documentation, which is where you should look first always: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Update_speed 5.2.10 Speed of UPDATE Queries Update queries are optimised as a SELECT query with the additional overhead of a write. The speed of the write is dependent on the size of the data that is being updated and the number of indexes that are updated. Indexes that are not changed will not be updated. Also, another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table. Note that, with dynamic record format, updating a record to a longer total length may split the record. So if you do this often, it is very important to OPTIMIZE TABLE sometimes. See section 4.5.1 OPTIMIZE TABLE Syntax. If you aren't doing an update of 50K per row, updating 3 columns instead of 1 will be much quicker (quoted from the manual). If you are doing 10,000 updates, read more of the manual on the syntax of UPDATE because you can delay your updates which allows MYSQL to update the table at its leisure which offers better performance. Peter, who reminds you to always read the manual (or less kindly RTFM!). On Sun, 10 Nov 2002, Leif K-Brooks wrote: I'm wondering how significant the performance differences between: mysql_query(update table set col1='val1' where whatever='whatever'); and mysql_query(update table set col1='val1',col2='val2',col3='val3'... where whatever='whatever'); -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Optimizing
On Sun, 10 Nov 2002, Chris Payne wrote: Hi there everyone, How does indexing work, and does it speed up small / average size DB's of around 20,000 records, with 12 columns per record? (Some columns being paragraphs of text). YES! 20,000 records is still a lot of records, and indexing on commonly/frequently searched, non-primary-key fields (like email address, last name, or zip code for example) will definitely help speed things up. ALSO, making sure fulltext indexing is on your text fields if you are searching your paragraphs of text will significantly help the queries. When you query your fulltext columns (anything of type TEXT), use this: select match(column1) against ('myquerytext') as blah,column1,column2,column3 from table order by blah desc This will return something like this: 2.393888483| column1 contents | column2 contents | column3 contents 1.938323487| column1 contents | column2 contents | column3 contents if they match 'myquerytext'. Search mysql.com documentation for fulltext indexing. Also, do you create an index on everything or just 1 item or or or :-) Depends. If you search on a single column almost always, then make an index on that one column. If you do select * from table where col1='x' and col2='y' then you'll want to create an index that includes col1 and col2. Again, read the manual on mysql.com on indexing. I've not looked at indexing but think it's time to start, but in a way even I can understand so I thought i'd ask you all here :-) Peter RTFM --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Having more than one id in the same field
Perhaps you could store all of the IDs in a varchar type (I'm assuming you're using some SQL database or another) separated by spaces or colons or anything else that wouldn't be in an ID. Then you can get the separate IDs by Exploding the result in PHP. That's probably not the best way to do this, but it's the first way I can think of. Hi. I have a database designing question to ask. I want to build a table of events. Among the other fields there must be a field that holds the 'responsible organization' of the event. This organization of course will be responsible for other events as well so I have to create another table that holds the organizations (id, name, phones, director etc) and then just pull the organization id to the events table. The problem is that it happens too often to have 2 organizations responsible for the same event so I'll have to add them both to the events table in the same record. How do you advice me to do that? I thought that I could use a text field to hold the ids and then when searching the database just change the MySQL command from ...where events.id='$id'... (As it would be if only one id was going to be used) to ...where '$id' in (events.ids)... or maybe something using LIKE. Do you think it can be done this way? Apart from the responsible organization I may have other fields in the same table having the same problem (for example: the event visitors are staying in one hotel and I want to hold control of the hotels as well. Maybe 2 hotels are used instead of one). If I solve my problem this way, do you think that it will be too difficult or 'heavy' to have more than one condition like this in my queries? Do you think of any other way? Thanx in advance Achilles -- Adam Atlas Your mouse has moved. Windows has to reboot for changes to take effect. [ OK ] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Having more than one id in the same field
I can't remember CD's 12 rules regarding relational databases, but a rough paraphrase of one of them is that you do not need to know anything about how data is stored to manipulate it, the database schema should provide all that information. Another one is that you never repeat information that can be broken out and stored in another table. You have to normalize your data, so in this case you would add a field, call it EventHost with fields PriKey EventId RspOrg EventId is a foreign key, referencing the field in the Event table which identifies the event, and RspOrg is a foreign key which references the key values in the, lets call it Organization (or Host) table. (PriKey is the primary key for the table, a field which hard experience has convinced me to have in all database tables, though in this case it may not be needed.) Indexes on these fields will assure that your queries will be optimized and you won't be condemned to sequential reads of the database while fields are evaluated by expressions like ...where '$id' in (events.ids)... . The Event table no longer needs a RspOrg field, and you can determine additional information about the host organization or the event through this table. You may also add fields such as HostLevel which indicates whether the RspOrg is a primary or secondary host, and so forth. It's time to Google for a primer on SQL databases and normalization, work through a couple, and to ask yourself what kind of questions you will be asking of your database. Regards - Miles Thompson At 12:41 PM 11/10/2002 +0200, you wrote: Hi. I have a database designing question to ask. I want to build a table of events. Among the other fields there must be a field that holds the 'responsible organization' of the event. This organization of course will be responsible for other events as well so I have to create another table that holds the organizations (id, name, phones, director etc) and then just pull the organization id to the events table. The problem is that it happens too often to have 2 organizations responsible for the same event so I'll have to add them both to the events table in the same record. How do you advice me to do that? I thought that I could use a text field to hold the ids and then when searching the database just change the MySQL command from ...where events.id='$id'... (As it would be if only one id was going to be used) to ...where '$id' in (events.ids)... or maybe something using LIKE. Do you think it can be done this way? Apart from the responsible organization I may have other fields in the same table having the same problem (for example: the event visitors are staying in one hotel and I want to hold control of the hotels as well. Maybe 2 hotels are used instead of one). If I solve my problem this way, do you think that it will be too difficult or 'heavy' to have more than one condition like this in my queries? Do you think of any other way? Thanx in advance Achilles -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Optimizing
You should index everything that is used in WHERE, ORDER and GROUP clauses. Means, if your queries do SELECT * FROM people WHERE name='maxim'; you should add an index on name field. Also, if you do something like this: SELECT * FROM people WHERE name='maxim' ORDER BY surname; then you better add an index on both of them, and, if this is the most common (heaviest) query for you do it together sequentially: (name, surname) I hope you get the idea. -- Maxim Maletsky [EMAIL PROTECTED] On Sun, 10 Nov 2002 13:46:33 -0500 Chris Payne [EMAIL PROTECTED] wrote: Hi there everyone, How does indexing work, and does it speed up small / average size DB's of around 20,000 records, with 12 columns per record? (Some columns being paragraphs of text). Also, do you create an index on everything or just 1 item or or or :-) I've not looked at indexing but think it's time to start, but in a way even I can understand so I thought i'd ask you all here :-) Thanks Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Winamp-Like Search
Would it be possible to create a Winamp-like search in a web-based form (the search basically checks your list of music as you type and pulls all the matches it finds)? Say you pull the results of a SQL Query and assign them to a variable. Can you take user input and compare it to the variable for matches before the user ever even submits the form? I don't think HTML elements can do that. I've never seen a drop down select that you can also type an entry into. Does that exist in HTML? Either way, it's nothing that PHP would have control over. I'd look around and ask on some DHTML/Javascript lists and see what they have to say. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Find out a pic size?
Hi, I'm working with a lot of picture in a website and the size is not always the same. I need some help on how to find out the size (width and height) of a picture. Is there any way to do this (especially with PHP)? I need it so I can calculate the width and height to be specified on the HTML img tag. Thanks, Hansen IndoInformatika
SV: [PHP-DB] Find out a pic size?
Hi, I'm working with a lot of picture in a website and the size is not always the same. I need some help on how to find out the size (width and height) of a picture. Is there any way to do this (especially with PHP)? I need it so I can calculate the width and height to be specified on the HTML img tag. Thanks, Hansen IndoInformatika Did you try the manual at all? GetImageSize() is what you're looking for. Regards Joakim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php