Re: [PHP-DB] SELECT query from two tables
I think what you mean to do is use IN(). And I would suggest table aliases. So it could look like this: SELECT * FROM ministry_directory md INNER JOIN ministry_directory_listing_categories mdlc ON md.entry = mdlc.ministry_directory_entry WHERE md.listing_type = 2 AND mdlc.ministry_directory_category_reference IN (10, 11) ORDER BY ministry_directory.name ASC Regards, Bruce Ron Piggott [EMAIL PROTECTED] 10/03/2008 10:33:13 a.m. I am wondering what is wrong with this syntax? SELECT * FROM ministry_directory INNER JOIN ministry_directory_listing_categories ON ministry_directory.entry = ministry_directory_listing_categories.ministry_directory_entry WHERE ministry_directory.listing_type = 2 AND ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 ORDER BY ministry_directory.name ASC It produces 0 results. In reality there is presently 1 record that should be found that has listing_type = 2 and ministry_directory_category_reference 10 and 11 in the ministry_directory_listing_categories table The table ministry_directory has the main contact information. entry is auto_increment; listing_type is an INT(1) column The table ministry_directory_listing_categories has 3 columns: reference which is auto_increment populated; ministry_directory_entry which is the common field between both tables, showing what the record belongs to ministry_directory_category_reference which is the reference number to how the directory listing was inputted / categorized. (IE If the person who completed the form select 2 of the 10 possible categories 2 records were created.) Is there a different way to word my query so I will be able to retrieve the record with two rows in table ministry_directory_listing_categories and 1 row in ministry_directory ? Thanks for the help guys. Ron -- 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] PHP 5.2 and MySQL on IIS
I originally sent this message to the general list, but maybe it's more appropriate here... I have just installed PHP 5.2.4 on Windows 2003 machine running IIS. Everything is fine except when I try to enable the MySQL extension (either php_mysqli.dll or php_mysql.dll), when I do a php_info(), at the bottom I get the message Error in my_thread_global_end(): 1 threads didn't exit. All my other extensions (curl, MSSQL, Oracle, etc.) load fine. It's only when trying to enable this one. I did a manual install, using the php-5.2.4-Win32.zip. This contains versions 5.2.4.4 of php_mysql.dll and php_mysqli.dll. I found a thread where someone said they fixed this by disabling php_iisfunc in the php.ini, but my php.ini doesn't even have this in it. Any ideas? Thanks. Regards, Bruce Regards, Bruce -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Preparing SQL insert statements from CSV files
And if you're using MS SQL Server, you can use BULK INSERT. Regards, Bruce Chris [EMAIL PROTECTED] 5/10/2007 3:30:13 p.m. T K wrote: Hi, I have to parse csv files and put them into database. I was able to parse the files by fgetcsv() and made an HTML table. But, when it comes to parse more closely and make INSERT INTO statement, then I'm having problems with, probably for-loop. Do you need to do anything to the data before it's inserted? Check out http://dev.mysql.com/doc/refman/5.0/en/load-data.html if you're using mysql - it can load a csv file directly (providing it's properly formatted etc). -- Postgresql php tutorials http://www.designmagick.com/ -- 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] PDO and MS Sql Server
I'm using PHP 5.1. The documentation for PDO doesn't list MS Sql server as one
of the drivers that support PDO but there is a php_pdo_mssql.dll which seems to
work so I'm using that.
I need to get the id of a new record just inserted. I can't use lastInsertId()
as I get a message saying it's not supported. So I've created a stored proc
that returns the id just created (code below). The insert works fine but the
$emailid variable is not populated. I can run the stored proc in query
analyzer and it outputs the id correctly so I know the stored proc works. All
the examples I see return strings, not sure if that has anything to do with it.
And as for the parameter length for the output parameter, I've tried nothing
as well as 9.
$stmt = $this-dbh-prepare(exec usp_EmailInsert :projectid, :mailfrom,
:mailto, :mailcc, :subject, :body, :mimefilename, :emailid);
$stmt-bindParam(':projectid', $projectid, PDO::PARAM_INT);
$stmt-bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
$stmt-bindParam(':mailto', $to, PDO::PARAM_STR, 500);
$stmt-bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
$stmt-bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
$stmt-bindParam(':body', $body, PDO::PARAM_LOB);
$stmt-bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500);
$stmt-bindParam(':emailid', $emailid, PDO::PARAM_INT, 9);
$stmt-execute();
echo \nemailid = $emailid\n;
Anyone have any ideas? Thanks.
Regards,
Bruce
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PDO and MS Sql Server
Thanks for replying. According to the PDO doco: If the database driver
supports it, you may also bind parameters for output as well as input. So
maybe this driver doesn't support it? I don't know. I'll try and get the
stored proc to return the value, but not sure how I'll capture that yet. If
that doesn't work, I'll just do a select @@identity.
Thanks again.
Regards,
Bruce
Chris [EMAIL PROTECTED] 6/07/2007 12:44:34 p.m.
Bruce Cowin wrote:
I'm using PHP 5.1. The documentation for PDO doesn't list MS Sql server as
one of the drivers that support PDO but there is a php_pdo_mssql.dll which
seems to work so I'm using that.
I need to get the id of a new record just inserted. I can't use
lastInsertId() as I get a message saying it's not supported. So I've created
a stored proc that returns the id just created (code below). The insert
works fine but the $emailid variable is not populated. I can run the stored
proc in query analyzer and it outputs the id correctly so I know the stored
proc works. All the examples I see return strings, not sure if that has
anything to do with it. And as for the parameter length for the output
parameter, I've tried nothing as well as 9.
$stmt = $this-dbh-prepare(exec usp_EmailInsert :projectid, :mailfrom,
:mailto, :mailcc, :subject, :body, :mimefilename, :emailid);
$stmt-bindParam(':projectid', $projectid, PDO::PARAM_INT);
$stmt-bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
$stmt-bindParam(':mailto', $to, PDO::PARAM_STR, 500);
$stmt-bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
$stmt-bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
$stmt-bindParam(':body', $body, PDO::PARAM_LOB);
$stmt-bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500);
$stmt-bindParam(':emailid', $emailid, PDO::PARAM_INT, 9);
$stmt-execute();
prepared statements are for ingoing queries, they can't put results from
that query into a binded parameter.
That is, when you bind a parameter it only works for the query TO the
database, they are not filled in for outgoing results.
Can you get your stored procedure to return the new id?
I'm not sure how this works for a stored procedure, but see the examples
here:
http://www.php.net/manual/en/function.PDO-prepare.php
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] die vs exception
Cheers, will check it out. Thanks. Regards, Bruce Chris Verges [EMAIL PROTECTED] 30/04/2007 4:46:23 p.m. The DB and MDB2 packages in the PEAR library use a third option, which is to return an Error subclass as the result rather than throwing it. Prons and cons for all, just wanted to throw in this option too. Btw, both the DB and MDB2 packages are already generic classes that support mssql as well as other DBMS programs, so if you're not familiar you may want to look up the details at http://pear.php.net. Good luck! Chris On 4/29/07 9:27 PM, Bruce Cowin [EMAIL PROTECTED] wrote: I have written a generic sql database class which contains a method RunQuery which uses mssql_query. To catch errors, should I include the or die clause or should I wrap it in a try/exception block? The first will stop the script running but for a generic class, I was thinking it'd be better to raise an exception and let the calling code handle it. I'd be interested to hear what everyone else does. Thanks. Regards, Bruce -- 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating drop down lists
Your select is only selecting the field 'root' but then you're trying to get
the field 'username' in your while loop. So, you can add username to your
select (or change it to *):
select root, username from training
select * from training
Regards,
Bruce
Scott [EMAIL PROTECTED] 11/03/2007 12:33:37 a.m.
Hi everyone!
I would like to create a drop down list with the info in my database.
I would also like to have more options so it will filter down to many
rows of data displayed then filtered down to a few rows of data that
said user is looking for.
Kind of like select a state then in the next box you can select the city.
but first thing is first.
I have tried a few things out there that I searched for but everything
I tried it will not populate the drop down list with the data in my
database.
So I know it somewhere along the lines of:
$query = mysql_query(SELECT root FROM training);
echo form action=something.php method=POSTselect name=Field;
while ($r = mysql_fetch_array($query))
{
$user = $r[username];
echo option value=$user$user/option;
}
echo /select;
or something like that but nothing populates
What is the proper code to get this done to populate and display what
I select from the drop down menu?
Say my DB name is: TUBA
My table name is: FLUTE
and I have xxx amounts of rows to filter through with three columns in
my mysql db.
Does that all make sense?
Thanks in advance!
You all rock!!
sb
--
AOL IM: Jestrix1
--
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] setting SQL variable
What about doing all of that in a stored proc and handle the transaction in
there? I've never done that with MySQL (only SQL Server) but I'm assuming it's
possible.
Regards,
Bruce
marga [EMAIL PROTECTED] 18/01/2007 10:37:04 p.m.
Hi,
I create a sql variable with php code. Is possible that it didn't works?
In order to maintain the integrity I open a transaction to do all
inserts. First insert a row in table1. I need these Insert ID
to make the follow Inserts in the relations tables. Is not possible to use
mysql_insert_id() and assing it in a php variable, because
mysql_insert_id() is executed after the COMMIT, I think.
I try to set a sql variable, do echo of the queries and paste the SQL
code in SQL console and works fine. But I have a SQL error if I execute
the php code. The mysql_error returns and mysl_errno return 0.
How to obtain the insert id of table1 and use it in the rest of inserts
into the transaction?
Thanks for advance!
I have a structure like this:
CREATE TABLE `table1` (
`id` int(11) NOT NULL auto_increment,
`test` int(11),
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
CREATE TABLE `table2` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
CREATE TABLE `table3` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
CREATE TABLE `table4` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
CREATE TABLE `rel_1` (
`id` int(11) NOT NULL auto_increment,
`id_table1` int(11) NOT NULL,
`id_table2` int(11) NOT NULL,
`id_table3` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `id_table1` (`id_table1`),
KEY `id_table2` (`id_table2`),
KEY `id_table3` (`id_table3`),
) ENGINE=InnoDB;
ALTER TABLE `rel_1`
ADD CONSTRAINT `rel_1_ibfk_1` FOREIGN KEY (`id_table1`) REFERENCES
`table1` (`id`),
ADD CONSTRAINT `rel_1_ibfk_2` FOREIGN KEY (`id_table2`) REFERENCES
`table2` (`id`),
ADD CONSTRAINT `rel_1_ibfk_3` FOREIGN KEY (`id_table3`) REFERENCES
`table3` (`id`);
CREATE TABLE `rel_2` (
`id` int(11) NOT NULL auto_increment,
`id_table1` int(11) NOT NULL,
`id_table4` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `id_table1` (`id_table1`),
KEY `id_table4` (`id_table4`)
) ENGINE=InnoDB;
ALTER TABLE `rel_2`
ADD CONSTRAINT `rel_2` FOREIGN KEY (`id_table4`) REFERENCES `table4`
(`id`),
ADD CONSTRAINT `rel_2` FOREIGN KEY (`id_table1`) REFERENCES `table1`
(`id`);
Part of code (simplified):
mysql_query(SET AUTOCOMMIT=0; BEGIN;);
mysql_query(INSERT INTO table1 (test) VALUES ('test');
if (mysql_errno()) {
$error = 1;
echo ERROR__1br;
}
else {
mysql_query(SET @id_last_table1=LAST_INSERT_ID(););
$query1 = INSERT INTO rel_1 (id_table2, id_table1) VALUES
(.$_POST['idtable2']., @id_last_table1 );;
$query2 = INSERT INTO rel_2 (id_table4, id_table1) VALUES
(.$_POST['idtable4']., @id_last_table1 );;
$query = $query1.$query2;
mysql_query($query);
if (mysql_errno() || $error==1) {
mysql_query(ROLLBACK);
echo ERROR_2.br;
}
else { mysql_query(COMMIT); }
--
Marga Vilalta
marga at ayuken dot com
Hov ghajbe'bogh ram rur pegh ghajbe'bogh jaj
--
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
