[PHP-DB] MySQL fulltext search with InnoDB table?
What is the best way to simulate a fulltext search on a MySQL table of type InnoDB? Do I index the searchable fields and use the LIKE() function with appropriate wildcards? What are the performance implications with this? Switching back to a table type of MyISAM is really not an option. Is there an "accepted" way of dealing with this issue? Thanks in advance for any tips, Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] php mysql dates
I would suggest creating the select dropdown so that the value is actually the numeric value of the month. i.e: January February . . . Then you can do a simple concat before adding to the database. --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Justin Patrin [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 3:44 PM To: Irm Jr Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] php mysql dates On Wed, 28 Jul 2004 14:39:01 -0700, Irm Jr <[EMAIL PROTECTED]> wrote: > Sure I understand Y-m-d. But won't the database cry because the Month > is in text format? (January, february)? Thanks again. > Whoops, I should read *all* the text of the question... Easiest way to deal with that, IMHO, is strtotime and date. $mysqlDate = date('Y-m-d', strtotime($month.' '.$day.', '.$year)); If you're using dates far in the past or future, though, this won't work. Better to use a date handling class, such as: http://pear.php.net/package/Date > Subject: Re: [PHP-DB] php mysql dates > > > > On Wed, 28 Jul 2004 14:30:24 -0700, Irm Jr <[EMAIL PROTECTED]> wrote: > > > > Hi all, currently I have a form which prompts for the user to choose a > > > date. The dropdown lists are stored into variables: > > > > $month//e.g. January, February, ... > > $day //e.g 1 - 31 > > $year //e.g. 2004 (four digits) > > > > then combined into > > > > $articleDate > > > > How can I manipulate this variable to be in such a format that I can > > insert the information into a DATE column in a mySQL database. > > > > Dates are a bit of a mystery to me as PHP and MySQL handle them > > differently. Your help is appreciated. > > > > Y-m-d > > -- > DB_DataObject_FormBuilder - The database at your fingertips > http://pear.php.net/package/DB_DataObject_FormBuilder > > paperCrane --Justin Patrin-- > -- DB_DataObject_FormBuilder - The database at your fingertips http://pear.php.net/package/DB_DataObject_FormBuilder paperCrane --Justin Patrin-- -- 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: Re: [PHP-DB] DB table creation question
Have you made sure that the parent table(s) is type INNODB as well? Also, in my experience, it is best to let the database be in charge of as many database functions as possible as it helps maintain the data's integrity. In other words, use foreign keys, indexing for speeding up searching, 'on delete cascade' to keep the database clean, etc. --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Vincent Jordan [mailto:[EMAIL PROTECTED] Sent: Monday, July 19, 2004 5:19 PM To: 'Matthew McNicol' Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] DB table creation question I think I may have gone over my head. I am fairly new to mysql and php. My host only allows access to mysql via phpmysql. I am used to using mysqlcc to connect to the db's and make changes. By default all tables created are MYISAM, when I try to input a INNODB table with foreign keys I either get syntax error or I believe it is errno(150). Anyhow, is there a piece of software or a webpage out there where I can input how I want the database to be made and it will spit out a .sql file that can be uploaded to phpmyadmin or would someone be kind enough to "hold my hand" as I attempt to create this with proper syntax myself. Another note, instead of using indexes and keys can I have tables relate to each other by php script or am I asking for trouble as it grows. This database will be small at first however I expect it to grow to over 5k of customer records after the third month and in stages of about 200 records monthly after that. Thanks for all the help everyone has provided thusfar. -Original Message- From: Matthew McNicol [mailto:[EMAIL PROTECTED] Sent: Monday, July 19, 2004 3:08 AM To: Vincent Jordan Cc: [EMAIL PROTECTED] Subject: [SPAM] Re: [PHP-DB] RE: [SPAM] Re: [PHP-DB] DB table creation question at the moment if a value is say inserted into the 'warranty_info' table you are just checking that the 'product_id' or 'cust_id' already exists in 'product_info' and 'customer_info'. you still have to populate them first, otherwise the referential integrity will return an error when you insert into 'warranty_info'. similarly if you delete a record from 'warranty_info' you might actually delete the associated customer record. is this what you want? at the moment it looks like the 'rma_info' table is not linked to any other table? personally, I would not go so far as to specify 'FOREIGN KEY ... REFERENCES' or 'ON UPDATE CASCADE ON DELETE RESTRICT' in the create table code. I would use the application code to make sure a customer exists before referencing them in the 'product_info' or 'warranty_info' tables. similarly I would use the application code to delete table record out of each appropriate table. I would use the primary and secondary index assuming you are working with more than 100 records in each table. Vincent Jordan wrote: > Does this seem to be correct? With this if value was inserted into a > table will the tables with the FK's automatically be updated? Here is > the DB design as I have it now. Let me know if all is correct please. > > CREATE TABLE customer_info ( >cust_id int (6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, >first_name varchar (50) NOT NULL, >last_name varchar (50) NOT NULL, >address varchar (50) NOT NULL, >address2 varchar (50) NULL, >city varchar (50) NOT NULL, >state varchar (50) NOT NULL, >zip varchar (50) NOT NULL, >home_phone varchar (15) NOT NULL, >work_phone varchar (15) NULL, >other_phone varchar (15) NULL, >email varchar (50) NOT NULL, >); > CREATE TABLE product_info ( >product_id int (6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, >cust_id int (6) NOT NULL, >model varchar (50) NOT NULL, >serial varchar (20) NULL, >FOREIGN KEY (customer_info_cust_id), > REFERENCES customer_info(cust_id), >ON UPDATE CASCADE ON DELETE RESTRICT, >INDEX (cust_id), >FOREIGN KEY (cust_id), >REFERENCES customer_info(cust_id), >); > CREATE TABLE warranty_info ( >warranty_id int (6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, >product_id int (6) NOT NULL, >cust_id (6) NOT NULL, >mfg_warranty tinyint (1) NOT NULL default '1', >gold_warranty tinyint (1) NOT NULL default '0', >gold_warranty_id int (16) NULL, >FOREIGN KEY (customer_info_cust_id), > REFERENCES customer_info(cust_id), >ON UPDATE CASCADE ON DELETE RESTRICT, >INDEX (cust_id), >FOREIGN KEY (cust_id), >REFERENCES customer_info(cust_id), >FOREIGN KEY (product_info_product_id), > REFRENCES product_info(product_id), >ON UPDATE CASCADE ON DELETE RESTRICT, >INDEX (product_id), >FOREIGN KEY (prod_id), > REFRENCES prod
RE: [PHP-DB] Ldap query problem
Do you have some code you can provide so we can get a better idea of what you're trying to do? My guess is that the filter isn't set up quite right (as in the base dn you're attaching to get the dn is not formatted correctly). What exactly are you trying to achieve? --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Ryan Jameson [mailto:[EMAIL PROTECTED] Sent: Thursday, July 15, 2004 3:46 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Ldap query problem My LDAP queries seem to have a problem with parenthesis, does anyone know how to fix this? Example... Criteria cn=Ryan Jameson* works fine returning user with cn=Ryan Jameson (MyDomain) But... Criteria cn=Ryan Jameson (MyDomain) returns nothing. :-\ <>< Ryan -- 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] SQL help
Have you tried lowering the fldBody as well? Like: SELECT autoQuesID,fldQuesTitle,fldBody FROM tblFAQ_Question WHERE LOWER(fldBody) LIKE '%$strSearchFor%'; And $strSearchFor has already been lowered, of course. --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Gabe [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 8:59 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] SQL help I'm using PHP with ADOdb ( and an MS Access 2000 db ) to write a simple SQL statement but was running into some case sensitivity issues. Here's my SQL currently: SELECT autoQuesID,fldQuesTitle,fldBody FROM tblFAQ_Question WHERE fldBody LIKE '%$strSearchFor%'; All I'm trying to do is have the users search string searched for in the "fldBody" field. However, I'm having problems trying to get it so that the search is case-insensitive. For instance: If I search on "Airline", I get 1 record. If I search on "airline", I get 0 records. I make the value of $strSearchFor lower case ( using strtolower() ), but I don't know how to get it so that the contents of the "fldBody" field is lower case also. I can't seem to find any functions or operators that remove the case-sensitivity. Any help would be much appreciated! -- 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] Request for help on (My)SQL Statement
You're right, of course, Micah. I guess I've been using my development server (which has MySQL 4.1a) for so long, that I've forgotten. :) I guess, then, that two queries would need to be used instead. --Nicole -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 27, 2004 5:00 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Request for help on (My)SQL Statement That would be the way to do it except that MySQL doesn't support sub-selects until version 4.1, which is in alpha still. -Micah On Tuesday 27 April 2004 04:05 pm, Swan, Nicole wrote: > Have you tried a nested query? I think your problem is that you're really > using information from two different tables. > > Maybe: > > UPDATE ngc_polling SET lastrundate = (SELECT max(post_time) FROM > nuke_phpbb_posts); > > > --Nicole > --- > Nicole Swan > Web Programming Specialist > Carroll College CCIT > Helena, Montana > (406)447-4310 > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 27, 2004 4:52 PM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Request for help on (My)SQL Statement > > :::Novice Alert::: > > I've done simple select and insert statements, so I suppose this is the > most complicated thing I've tried to do. but seems to me it should be > pretty easy. But I'm a designer by background, not a developer or > programmer. I've got everything else I'm trying to do working. > > I'm trying to take the largest value out of one table and update another > table. The former is a PHPBB table and the latter is a table I created just > to store the value of the date of the most recent post for use in something > else. > > UPDATE ngc_polling SET lastrundate = max(nuke_phpbb_posts.post_time) > > It returns "Invalid use of group function." > > I've been looking at the documentation on MySQL.com to no avail. I'm > testing my query in phpMyAdmin before I try to implement it elsewhere. This > is what I'm trying to do, any help besides "RTFM" (been doing that) would > be greatly appreciated. > > Thanks! > > John > > John Bedard | Interaction Designer (SWEIII) | Northrop Grumman Mission > Systems | Helena, Montana, U.S.A. | [EMAIL PROTECTED] | 406.443.8630 | > Fax 406.443.8601 | -- 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] Request for help on (My)SQL Statement
Have you tried a nested query? I think your problem is that you're really using information from two different tables. Maybe: UPDATE ngc_polling SET lastrundate = (SELECT max(post_time) FROM nuke_phpbb_posts); --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT Helena, Montana (406)447-4310 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 27, 2004 4:52 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Request for help on (My)SQL Statement :::Novice Alert::: I've done simple select and insert statements, so I suppose this is the most complicated thing I've tried to do. but seems to me it should be pretty easy. But I'm a designer by background, not a developer or programmer. I've got everything else I'm trying to do working. I'm trying to take the largest value out of one table and update another table. The former is a PHPBB table and the latter is a table I created just to store the value of the date of the most recent post for use in something else. UPDATE ngc_polling SET lastrundate = max(nuke_phpbb_posts.post_time) It returns "Invalid use of group function." I've been looking at the documentation on MySQL.com to no avail. I'm testing my query in phpMyAdmin before I try to implement it elsewhere. This is what I'm trying to do, any help besides "RTFM" (been doing that) would be greatly appreciated. Thanks! John John Bedard | Interaction Designer (SWEIII) | Northrop Grumman Mission Systems | Helena, Montana, U.S.A. | [EMAIL PROTECTED] | 406.443.8630 | Fax 406.443.8601 | -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] more detail..passing array_var with $_get
I thought I would mention that if you just "echo" or "print" an array, just the word "array" is shown. If you do a var_dump($myArray) on the array, the values should be shown. Otherwise you can get the values as follows: $myArray = $_GET["b"]; //then get and print values foreach( $myArray as $num => $val ) { echo "$val"; //or do whatever with the value -- add to db, etc. } --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Sukanto Kho [mailto:[EMAIL PROTECTED] Sent: Monday, April 26, 2004 3:55 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] more detail..passing array_var with $_get Hi all, sorry for making a little mess up just now.. My problem is I want to pass var array with $_get.. eg : $a=array() then i pass to other pages www.main.com?b=$a after that echo $b the result is "array"... just that... I want to make a product quotation so my purpose is that i add all product 1 by 1 in my local pc then just adding to database in server at once... the best way is using array right?? if i using session then it just the same result thanx -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Displaying Date from Value in MySQL DB
In PHP you could do something like: $mydate = date("l, F jS, Y", strtotime($row["eventdate"])); echo $mydate; http://us4.php.net/manual/en/function.date.php gives a full listing of possible formatting options. --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Justin @ Dreaming in TO [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 12:28 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Displaying Date from Value in MySQL DB Hey All, Looking to try to figure at small problem. This is the SQL query I am using to show the next event on a page: SELECT ditoevents.eventstatus, ditoevents.eventdate, DATE_FORMAT(ditoevents.eventdate, '%a, %b %d %Y'), ditoevents.eventtime, ditoevents.eventlocation, ditoevents.topic, ditoevents.presenter FROM ditoevents WHERE ditoevents.eventstatus = 'next' This is the structure of the table in the MySQL db: CREATE TABLE ditoevents ( ID char(3) NOT NULL default '', eventID char(2) NOT NULL default '', eventstatus varchar(4) NOT NULL default '', eventdate date NOT NULL default '-00-00', eventtime time NOT NULL default '00:00:00', eventlocation text NOT NULL, topic text NOT NULL, presenter text NOT NULL ) TYPE=MyISAM; The problem is, the date value that's being pulled from the DB is being displayed as 2004-05-12. I would like the date to be displayed as, Wednesday, May 12th, 2004. How do I do this with PHP. I looked at some of the date functions, and I'm a bit confused. Thanks, Justin -- 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] putting strings together with a linefeed
"\r\n" should give a carriage return. i.e: $mytext = 'what up doc'; $mytext .= '\r\n not much'; --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Hull, Douglas D [mailto:[EMAIL PROTECTED] Sent: Friday, April 16, 2004 10:24 AM To: Note To php mysql List (E-mail) Subject: [PHP-DB] putting strings together with a linefeed For example, say I have a variable $mytext = 'what up doc'. Now I want to add the text 'not much' to the end of this variable. But I want to place a return or linefeed after the original text so when I echo $mytext it looks like: what up doc not much I tried: $mytext = 'what up doc'; $mytext .= chr(10) . 'not much';I also tried chr(13) 10 is linefeed and 13 is carriage return. Thanks for your previous help as well. Doug -- 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] RE:[PHP-DB] Forms and more forms?
What does your HTML look like? Have you defined the delete checkbox as an array? For example: Notice the brackets to indicate an array. --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 05, 2004 2:11 AM To: [EMAIL PROTECTED] Subject: Spam:[PHP-DB] Forms and more forms? I'm talking to a MySQL database that contains info on all downloads from our site. I list all these entries, on a PHP page. The table structure etc, are static, but the fields retrieved, are dynamic, based on the users search criteria. Within each result, I've added a small form, that posts that data, to an external site (salesforce.com). Each field looks like this: http://www.risk.sungard.com/del.gif But one can have entries on a page.. At present, a user can submit a field to salesforce one at a time, no probs, and I'll probably wanna keep it that way. However.. TO enable multiple deletions, I must place the whole table in another form. What happens is that no matter how many deletions I select, it only picks up the one at the top of the page, or none, if the top one isn't selected. I'm using the following PHP... if ($submit == 'DELETE SELECTED') { foreach($_POST[select] as $del) { $res = mysql_query("DELETE FROM $table_name WHERE id='$del'"); } } But I presume that the embedded forms that talk to sales force, are disrupting it... So I need to either: 1. find out how to let the page detect what's selected anotehr way.. OR 2. enable multiple post to salesforce.com, WITHOUT opening potentially dozens of new windows (one for each submision) Any takers on a unique way to acheive this? Tris.. (P.S. Glad you asked ;-) ) * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- 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] How do you make an IN span multiple tables?
Have you tried using an 'OR' operator? SELECT * FROM table1, table2, table3 WHERE table1.threadref IN (1,2,3,4,5) OR table2.threadref IN (1,2,3,4,5) OR table3.threadref IN (1,2,3,4,5) --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Thursday, March 04, 2004 7:36 AM To: php-db Subject: Spam:[PHP-DB] How do you make an IN span multiple tables? Hi, I'm a bit stuck with the following: I need to select data from 3 different tables and want to do it via a SELECT ... IN query. Each table has a primary key called thread_ref. I would like to do something like this: SELECT * FROM table1, table2, table3 WHERE thread_ref IN (1,2,3,4,5,6,7,8,9,10) Now the problem is that MySQL complains about the thread_ref being ambiguous - which I know that it is. But putting a table name in-front of it will limit the IN query to that specific table and cause no results to be returned. It is possible that not all of the tables will contain all of the thread_ref's in question. Does anyone have any ideas or is it just not possible using this method? I'm not using MySQL 4 otherwise I would have looked at using a UNION. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- 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] mysql_num_rows
>From a quick glance, I noticed that you have commented out the code that does the >actual query against the MySQL database. This is the line: // $result = mysql_query($query, $db); Without this statement, there is no query to the database. It should be: $result = mysql_query($query); //without specifying a database link, it will use the available one The $result variable will then hold a result identifier that should be used throughout your code from then on (i.e. any place you currently have $query). For example: mysql_num_rows($result) or mysql_result($result, 0, 'user_id'); --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Craig Hoffman [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 12:50 PM To: [EMAIL PROTECTED] Subject: Spam:[PHP-DB] mysql_num_rows Perhaps someone could look at this function and help me trouble shoot it? This function notifies the user of their new passwd. I keep getting an error message on this page (see below). What's strange is the script seems to be working because i am getting an email with my new password. Here is the error message I am receiving: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /Users/choffman/Sites/www/cyclistsedge/include/ forgot_password_functions.php on line 70 suggestions? Anything will be helpful. Much Thanks CH function notify_password($username, $password) // notify the user that their password has been changed { $query ="SELECT username FROM users WHERE username=('$username')"; // $result = mysql_query($query, $db); if (!$query) { return false; // not changed } line 70 => else if (mysql_num_rows($query)==1) $user_id = mysql_result($query, 0, 'user_id'); $from = "From: [EMAIL PROTECTED] \r\n"; $mesg = "Your password has been changed to $password \r\n" ."You might want to change it next time you log in. \r\n"; if (mail($username, 'Cyclists Edge login information', $mesg, $from)) return true; else return false; } __ Craig Hoffman - eClimb Media v: (847) 644 - 8914 f: (847) 866 - 1946 e: [EMAIL PROTECTED] w: www.eclimb.net _ -- 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