Re: [PHP-DB] ORDER BY relevance DESC query
Hey friend.
The problem is here:
( SELECT `reference`, `organization` FROM `ministry_profiles`...
In a subselect, you must select just one column. You are selecting two
reference and organization.
If you really need both, so you must do two subselects: one for
reference and another for organization.
Cheers!
Adriano Laranjeira.
São Paulo - Brazil.
On Tue, 12 Apr 2011 04:00:15 -0400
Ron Piggott ron.pigg...@actsministries.org wrote:
I am programming the search feature for a directory. I am trying to
make the query display the results in order of relevance (Greatest to
least). I thought I had to perform the query similar to a column
being selected in order to sort the results by relevance. What
changes do I need to make to my query for it to work correctly?
The mySQL query is giving me the error message:
#1241 - Operand should contain 1 column(s)
The FULLTEXT index contains:
organization
address_line_1
address_line_2
city
province_state
postal_zip_code
country
telephone
toll_free
fax
email
website
Presently the mySQL query is:
SELECT `reference`, `organization` ,
( SELECT `reference`, `organization` FROM `ministry_profiles`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`,
`ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`,
`ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
AND `live` = 1
) AS relevance
FROM `ministry_profiles`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`,
`ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`,
`ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
AND `live` = 1
ORDER BY relevance DESC
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Prepared Statements # of rows
Hey friend!
You can use the PDOStatement::RowCount, but there is a problem
(extracted from the PHP Manual,
http://php.net/manual/en/pdostatement.rowcount.php):
PDOStatement-rowCount - Returns the number of rows affected by the
last SQL statement. If the last SQL statement executed by the
associated PDOStatement was a SELECT statement, some databases may
return the number of rows returned by that statement. However, this
behaviour is not guaranteed for all databases and should not be relied
on for portable applications.
You should test if the expected behavior happens with this method, or
use the method below (less elegant):
$sql = SELECT COUNT(*) FROM fruit WHERE calories 100;
if ($res = $conn-query($sql)) {
/* Check the number of rows that match the SELECT statement */
if ($res-fetchColumn() 0) {
/* Issue the real SELECT statement and work with the results
*/
$sql = SELECT name FROM fruit WHERE calories 100;
foreach ($conn-query($sql) as $row) {
print Name: . $row['NAME'] . \n;
}
}
/* No rows matched -- do something else */
else {
print No rows matched the query.;
}
}
Best regards,
Adriano Laranjeira.
On Fri, 4 Mar 2011 07:30:51 -0500
Ron Piggott ron.pigg...@actsministries.org wrote:
When I used Prepared Statements how do I check for the # of rows
found (Equal to mysql_numrows )?
IE Following the command:
$stmt-execute() or die(print_r($stmt-errorInfo(), true));
Ron
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] CSV to MyDB
Hey friend! You can create a DB table with the same structure of your CSV and store it like a normal table, using the mysqlimport command: http://dev.mysql.com/doc/refman/5.5/en/mysqlimport.html In this way, you can do SELECT's and all DML commands. If you want to store the file as you receive it, that'd be good to create three columns: an ID (AUTONUM), a date_load and a BLOB field called content, where I'd store the CSV. And I guess you should use BLOB. A quote from http://dev.mysql.com/doc/refman/5.0/en/blob.html: BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set. My $0.02, Adriano Laranjeira. São Bernardo do Campo - Brazil. On Fri, 25 Feb 2011 09:06:47 -0600 Karl DeSaulniers k...@designdrumm.com wrote: Hello everyone, Hope your day is going well. I have a (hopefully) quick question. What is the best way to store a CSV file in a MySQL database? As a varchar, blob, longtext or other? I would like to minimize the amount of fields in the table, so I was leaning towards a longtext or blob. But I don't know the advantages or disadvantages to using these. The text is a excel price list that is exported to a csv and the data will be sent to repopulate a html table that has the same amount of fields. the only catch is some of the fields are descriptions (text) and some are just prices (numbers). Hope this is not an overload of info, just want to inform complete. TIA, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
Hello! I didn't use any DROP statements. Do exactly as following: mysql CREATE TABLE stats2 LIKE stats; mysql ALTER TABLE stats2 ADD COLUMN id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id); mysql INSERT INTO stats2 SELECT *, 0 FROM stats ORDER BY The first line copies the structure of table stats to a new table called stats2; The second one, create a new field called id; The third inserts all records from stats to stats2, in correct order. After all, you need drop the table stats and rename the table stats2 to stats: mysql DROP TABLE stats; mysql RENAME TABLE stats2 TO stats; Be careful, I don't know your environment. []'s Adriano! On Tue, 07 Sep 2010 18:10:34 +0200 Kapu kapuorigi...@gmail.com wrote: You are missing a semicolon after the first alter statement. Kapu On 7. 9. 2010 18:05, Ron Piggott wrote: I am receiving the following error Adriano: SQL query: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats` ; MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `stats2` LIKE `stats`' at line 2 The complete commands were: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats`; ALTER TABLE `stats2` ADD COLUMN `visits` INT( 25 ) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(`visits`) FIRST; INSERT INTO `stats2` SELECT *, 0 FROM `stats` ORDER BY `initial_access`; I don't understand the error, your way of creating a table is new to me. Did something small get missed? 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
Re: [PHP-DB] auto_increment
Hey friend! I can't see another way to fix your table without a processing (like a stored procedure, script, etcetera). But I believe there is a workaround, which involves a creation of another table. See this: mysql CREATE TABLE stats2 LIKE stats; mysql ALTER TABLE stats2 ADD COLUMN id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id); mysql INSERT INTO stats2 SELECT *, 0 FROM stats ORDER BY initial_access; Don't forget to see the correct type of INT which should be used. Cheers, Adriano! On Tue, 7 Sep 2010 05:29:07 -0400 Ron Piggott ron.pigg...@actsministries.org wrote: I am wondering if something like the following will work in mySQL: ALTER TABLE `stats` ADD `visits` INT( 25 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ORDER BY `initial_access` ASC This particular syntax won't work though. initial_access is a column that contains a unix timestamp. I am trying to get the auto_increment value to be added in order of sequence of when the visits occurred. Thank you. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP ERP Applications Need Suggestions????
Hello! You should look at same good stable ERP's PHP based, and start your new project from one of them. By example: * BlueErp * Dolibarr * GNU Enterprise * WebERP Cheers, Adriano Laranjeira. On Mon, 28 Jun 2010 17:17:26 +0530 Mohamed Hashim nmdhas...@gmail.com wrote: *Hello everyone,* *I just want to develop a big ERP applications.Can anyone tell me which framework will be better for ERP (HR,Finance,Inventory,Production,Administration etc etc) means CakePHP,Codeigniter and also database PostgreSql or Mysql or shall i go with Core PHP? Thanks in advance Kindly do the reply my friends I -- Regards Mohamed Hashim.N Mobile:09894587678* -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
