[PHP-DB] Case sensitive search
Hi, I have a simple table: test ( id int unsigned NOT NULL auto_increment, data varchar(30) default NULL, PRIMARY KEY (id)) with two simple records: id data 1 a 2 A When I perform select * from test where data='a' - it return me both rows. By default in MySQL comparing of strings is case-insensitive. How can I perform a case sensitive search in text fields ? Tnanks in advance, Rosen -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Case sensitive search
Rosen wrote: Hi, I have a simple table: test ( id int unsigned NOT NULL auto_increment, data varchar(30) default NULL, PRIMARY KEY (id)) with two simple records: id data 1 a 2 A When I perform select * from test where data='a' - it return me both rows. http://dev.mysql.com/doc/mysql/en/Case_Sensitivity_Operators.html Doug -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Importing CSV to PostgreSQL
Can someone lend some guidance or point me to some docs on how to import an uploaded CSV file into a PostgreSQL table? I will need to evaluate the imcoming data line by line and set different columns to values depending on the evaluations. I would like to do this in a transaction so that if anything fails, all fails. Right now, I am looking to read each line with 'fopen, fgets, etc.' and split the CSV's into an array and use pg_copy_from to insert records. Not sure how I can put that into a transaction or if that is actually the easiest or most effcient way to do it. Since this is the first time doing anything like this, looking for anyone with this experience to offer some guidance? -- Robert -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: Case sensitive search
Two options: 1. Use ALTER to make the field BINARY. 2. Use the BINARY modifier in your SELECT select * from test where BINARY data='a' -Original Message- From: Rosen [mailto:[EMAIL PROTECTED] Sent: Sun 7/18/2004 2:45 AM To: [EMAIL PROTECTED] Cc: Subject:Case sensitive search Hi, I have a simple table: test ( id int unsigned NOT NULL auto_increment, data varchar(30) default NULL, PRIMARY KEY (id)) with two simple records: id data 1 a 2 A When I perform select * from test where data='a' - it return me both rows. By default in MySQL comparing of strings is case-insensitive. How can I perform a case sensitive search in text fields ? Tnanks in advance, Rosen
[PHP-DB] RE: Rewrite value from form data
I have an even easier solution - why not just use three form fields, run a reg exp test on each individually for appropriate # of digits and then concatenate with - prior to insert? input type=text name=area size=3 maxlength=3 - input type=text name=prefix size=3 maxlength=3- - input type=text name=suffix size=4 maxlength=4
[PHP-DB] php, javascript and db - your help is needed
Hello, I have a HTML form with 2 select boxes, lets say students and courses. When the from loads for the first time, I fill the students select box with data from database. The courses selectbox remains empty. When the user selects a value from the students selectbox (onchange event), I should go to the db to reterive the courses for the selected student and fill the courses selectbaox. I found it very complicated to do the interaction between javascript and php: apparently, I have to submit the from for the selected student id to be passed as a parameter to php (so that php can access the db with the student id and retrieve his courses), and at the same time I want the form to stay with the list of students, and the selected student. This should not be that hard, should it?! this is something trivial, I hope? I also tried using cookies, with no success. If someone knows how to do that, please let me know. Best Regards, G. Cohen -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php, javascript and db - your help is needed
You could use a if/while construct. Something like:
select name=\courses\
?php
if ($_POST['studentselect']){
$result = mysql_query(SELECT courses FROM students WHERE student_name =
' . $_POST['studentselect'] . ');
while ($row = mysql_fetch_assoc($result))
{
echo option value=.$row['course']..$row['course']./option;
}
}
?
/select
Basically you would submit the form, and if a student was selected, it
would populate the list with their courses from the database.
--
Marcjon
- Original message -
From: G. Cohen [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Date: Sun, 18 Jul 2004 19:30:49 +0300
Subject: [PHP-DB] php, javascript and db - your help is needed
Hello,
I have a HTML form with 2 select boxes, lets say students and courses.
When
the from loads for the first time, I fill the students select box with
data
from database. The courses selectbox remains empty.
When the user selects a value from the students selectbox (onchange
event),
I should go to the db to reterive the courses for the selected student
and
fill the courses selectbaox. I found it very complicated to do the
interaction between javascript and php: apparently, I have to submit the
from for the selected student id to be passed as a parameter to php (so
that
php can access the db with the student id and retrieve his courses), and
at
the same time I want the form to stay with the list of students, and the
selected student. This should not be that hard, should it?! this is
something trivial, I hope?
I also tried using cookies, with no success.
If someone knows how to do that, please let me know.
Best Regards,
G. Cohen
--
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] DB table creation question
I am creating a database to keep track of our warranty returns. Currently we do not have anything to track warranty info besides a big excel file. I would like to keep customer data, product info and RMA data in separate tables but still keep them related. If I had table customers: custid Firstname Lastname Address Address2 City State Zip Phone Email And table RMA Rmaid Assignedto Loggedby Customerid Date Timespent Status Priority Closedby Completedby Dateclosed Billable Billed Cost Lastmodifiedby Lastmodifieddate What I want to ensure is that if john smith fills out an RMA request online a customer id is assigned to John, the RMA number is associated with Johns customerid and I need to figure out how to assign a product to John. Also if john has more than one product assign products so when I pull up Johns information I can see all of Johns products owned. I am running mysql latest version.. Any help would be greatly appriciated
[PHP-DB] Re: Case sensitive search
Hi, i suggest you to use the LIKE operator instead of the relational operator. Rui Cunha Rosen writes: Hi, I have a simple table: test ( id int unsigned NOT NULL auto_increment, data varchar(30) default NULL, PRIMARY KEY (id)) with two simple records: id data 1 a 2 A When I perform select * from test where data='a' - it return me both rows. By default in MySQL comparing of strings is case-insensitive. How can I perform a case sensitive search in text fields ? Tnanks in advance, Rosen * * Rui Pedro Cunha * * Dpto. de Ciências e Tecnologias * * Universidade Autónoma de Lisboa * * Rua de Santa Marta, 56, * * 1169-023 Lisboa * * Telefone (+351) 21 317 76 35/49 * * Fax (+351) 21 353 37 02 * * Url : http://www.ual.pt/dct/ * * E-mail: [EMAIL PROTECTED] * * -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php, javascript and db - your help is needed
On Sun, 18 Jul 2004 13:24:01 -0700, Marcjon [EMAIL PROTECTED] wrote:
You could use a if/while construct. Something like:
select name=\courses\
?php
if ($_POST['studentselect']){
$result = mysql_query(SELECT courses FROM students WHERE student_name =
' . $_POST['studentselect'] . ');
while ($row = mysql_fetch_assoc($result))
{
echo option value=.$row['course']..$row['course']./option;
}
}
?
/select
Basically you would submit the form, and if a student was selected, it
would populate the list with their courses from the database.
--
Marcjon
- Original message -
From: G. Cohen [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Date: Sun, 18 Jul 2004 19:30:49 +0300
Subject: [PHP-DB] php, javascript and db - your help is needed
Hello,
I have a HTML form with 2 select boxes, lets say students and courses.
When
the from loads for the first time, I fill the students select box with
data
from database. The courses selectbox remains empty.
When the user selects a value from the students selectbox (onchange
event),
I should go to the db to reterive the courses for the selected student
and
fill the courses selectbaox. I found it very complicated to do the
interaction between javascript and php: apparently, I have to submit the
from for the selected student id to be passed as a parameter to php (so
that
php can access the db with the student id and retrieve his courses), and
at
the same time I want the form to stay with the list of students, and the
selected student. This should not be that hard, should it?! this is
something trivial, I hope?
I also tried using cookies, with no success.
If someone knows how to do that, please let me know.
Best Regards,
G. Cohen
PHP is a server-side language, so any further processing can only be
done when the browser sends a new request. You have to refresh *some*
page to get new data.
It is possible to use a hidden iframe to send a request, then parse it
with JS and display it in the main page, but this isn't trivial.
--
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
Re: [PHP-DB] DB table creation question
use the mysql 'auto increment' on a 'int' 'customer_id' field to generate a unique customer id. the RMA number is associated with a 'customer_id' since the 'customer_id' field is in both the customers table and the RMA table. Note that currently you have 'custid' and 'Customerid' respectively which you should change so that they are uniform (it helps with database maintenance is foreign keys are spelt the same as the primary key equivalent). to assign a product to a customer, first create a products table which defines each product. each product would have a unique 'product_id'. second, create a table which will link the products (zero, one or more) a customer has selected to the 'rma_id'. such tables would have the following structure: table: product product_id product_desc ... table: rma_product rma_id product_id table: customer customer_id firstname lastname ... table: rma rma_id assignedto loggedby customer_id ... Do you follow? Do you understand in what order you should populate the tables and how you can now create SQL queries to get the information you need? Vincent Jordan wrote: I am creating a database to keep track of our warranty returns. Currently we do not have anything to track warranty info besides a big excel file. I would like to keep customer data, product info and RMA data in separate tables but still keep them related. If I had table customers: custid Firstname Lastname Address Address2 City State Zip Phone Email And table RMA Rmaid Assignedto Loggedby Customerid Date Timespent Status Priority Closedby Completedby Dateclosed Billable Billed Cost Lastmodifiedby Lastmodifieddate What I want to ensure is that if john smith fills out an RMA request online a customer id is assigned to John, the RMA number is associated with Johns customerid and I need to figure out how to assign a product to John. Also if john has more than one product assign products so when I pull up Johns information I can see all of Johns products owned. I am running mysql latest version.. Any help would be greatly appriciated -- _ Matthew McNicol yellowmarker.co.uk PHP / MySQL web development -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Case sensitive search
Rui Cunha wrote: i suggest you to use the LIKE operator instead of the relational operator. You should try your suggestions before you.. er, suggest them: mysql select 'a' like 'a'; +--+ | 'a' like 'a' | +--+ |1 | +--+ 1 row in set (0.00 sec) mysql select 'a' like 'A'; +--+ | 'a' like 'A' | +--+ |1 | +--+ 1 row in set (0.00 sec) -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Not Like
I am trying to figure out how to display something where the condition is not like im a MySQL query. The query is select * from class where classID like '_00'order by classID; How would I make the like into a not like (aka negating it. The ! does not work)? I couldn't find anything in the MySQL manual. Thanks, Cole -- This message has been scanned for viruses and dangerous content by MailScanner on mail.ashcraftfamily.net, and is believed to be clean. Please report any deviance from this condition immediately to the AFN Administrator at [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Not Like
Cole S. Ashcraft wrote: I am trying to figure out how to display something where the condition is not like im a MySQL query. The query is select * from class where classID like '_00'order by classID; How would I make the like into a not like (aka negating it. The ! does not work)? I couldn't find anything in the MySQL manual. SELECT * FROM class WHERE classID NOT LIKE '_00' ORDER BY classID Do I get a doh! that was obvious prize?? ;) -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Not Like
On Sun, 2004-07-18 at 15:00, John W. Holmes wrote: Cole S. Ashcraft wrote: I am trying to figure out how to display something where the condition is not like im a MySQL query. The query is select * from class where classID like '_00'order by classID; How would I make the like into a not like (aka negating it. The ! does not work)? I couldn't find anything in the MySQL manual. SELECT * FROM class WHERE classID NOT LIKE '_00' ORDER BY classID Do I get a doh! that was obvious prize?? ;) Sorry. Completely forgot about that. And I say I know how to use MySQL. Wow. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals – www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- This message has been scanned for viruses and dangerous content by MailScanner on mail.ashcraftfamily.net, and is believed to be clean. Please report any deviance from this condition immediately to the AFN Administrator at [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: [SPAM] Re: [PHP-DB] DB table creation question
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 product_info (product_id), ); CREATE TABLE rma_info ( rma_id int (6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, assigned_to varchar (50) NULL, logged_by varchar (50) NULL, date datetime NOT NULL, time_spent datetime (6) NULL, status varchar (50) NOT NULL, priority varchar (50) NULL, closed_by varchar (50) NULL, completed_by varchar (50) NULL, date_closed datetime (6) NULL, billable tinyint(1)NULL, billed tinyint (1) NULL, cost varchar (6) NULL, last_modified_by varchar (50) NULL, last_modified_date datetime NULL, ); -Original Message- From: Matthew McNicol [mailto:[EMAIL PROTECTED] Sent: Sunday, July 18, 2004 5:46 PM To: Vincent Jordan Cc: [EMAIL PROTECTED] Subject: [SPAM] Re: [PHP-DB] DB table creation question use the mysql 'auto increment' on a 'int' 'customer_id' field to generate a unique customer id. the RMA number is associated with a 'customer_id' since the 'customer_id' field is in both the customers table and the RMA table. Note that currently you have 'custid' and 'Customerid' respectively which you should change so that they are uniform (it helps with database maintenance is foreign keys are spelt the same as the primary key equivalent). to assign a product to a customer, first create a products table which defines each product. each product would have a unique 'product_id'. second, create a table which will link the products (zero, one or more) a customer has selected to the 'rma_id'. such tables would have the following structure: table: product product_id product_desc ... table: rma_product rma_id product_id table: customer customer_id firstname lastname ... table: rma rma_id assignedto loggedby customer_id ... Do you follow? Do you understand in what order you should populate the tables and how you can now create SQL queries to get the information you need? Vincent Jordan wrote: I am creating a database to keep track of our warranty returns. Currently we do not have anything to track warranty info besides a big excel file. I would like to keep customer data, product info and RMA data in separate tables but still keep them related. If I had table customers: custid Firstname Lastname Address Address2 City State Zip Phone Email And table RMA Rmaid Assignedto Loggedby Customerid Date Timespent Status Priority Closedby Completedby Dateclosed Billable Billed Cost Lastmodifiedby Lastmodifieddate What I want to ensure is that if john smith fills out an RMA request online a customer id is assigned to John, the RMA number is associated with Johns customerid and I need to figure out how to assign a product to John. Also if john has more than one product assign products so when I pull up Johns information I can see all of Johns products owned. I am running mysql latest version.. Any help would be greatly appriciated -- _ Matthew McNicol yellowmarker.co.uk PHP / MySQL web development
[PHP-DB] Reducing Strings to a certain length
How would you reduce a string to a specified length? Say reduce 600 to 60 or abc to ab? Is there a PHP function for this? Will I have to write my own code? Cole -- This message has been scanned for viruses and dangerous content by MailScanner on mail.ashcraftfamily.net, and is believed to be clean. Please report any deviance from this condition immediately to the AFN Administrator at [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
