mysql

Is the Sphinx beta okay to use in production?

I'm having to recompile Sphinx (thanks to this issue – if you can help I'd be impressed!). Is the Sphinx beta ready for use in a production environment? I know that Sphinx 0.9.9 has been feature-frozen for a long time, and the Sphinx beta has a number of really wonderful features. But beta is scary. Do I have anything to worry about? ...

Mysql - Count how many rows have a certain number?

Hello, I have a query that checks a tables and groups all entries from a user and counts those entries SELECT username, count(userid) FROM orders GROUP BY userid This returns a list of username's and how many orders they have submitted username count(userid) ------------------------ test 1 test2 1 tes...

New to SQL, how do I use results from a SELECT call on TableA to SELECT against TableB?

I'm using phpMyAdmin to query the database. I want to select all the entries in TableA where Date='2010-08-01'. Then I want to use the SubID attributes of the items in the result table to find all the entries in TableB with matching SubIDs. I know I have to use a JOIN somewhere, but I am not sure how or where. I searched StackOverflo...

Finding data that's missing from the database

I need to figure out some clever MySQL snippet that will allow me to easily see two tables, the ids in the table if they exist or NULL or empty if they don't exist. I have a users table and a legacy table and outside of manual comparison I can't figure out how to make them appear in a table together so I can compare. What I would love ...

pdo fetch within a fetch error

I am trying to run the following script (unsuccessfully): $variables = $db->query("SELECT * FROM table1 WHERE Session_ID = '$sess1'"); while($row = $variables->fetch()) { //FETCH DATA $id= $row["ID"]; $info = $db->query("SELECT * FROM table2 WHERE ID = $id"); while($row2 = $info->fetch()) { $name = $row2["FNAME"]." ".$row2["LNAM...

php mysqli_insert_id($link) convert/retain

Hi i have a form (X) that inserts data into a mysql table (A) and than redirects to another form (Y) which will insert data into another table (B) but i need to use tables A's last inserted row. I know i can get it from using the mysqli_insert_id function but when i submit to form Y and re show form Y is messes up because the $ID value...

MySQL - Joining a table based on 2 keys from a Key/Value table

I have a table for settings, which can be shown as: object_id (int) key (varchar) value (text) I am trying to grab the object_id that has key's equal to 2 items. SELECT `object_id` FROM `settings` WHERE `key` = 'A' AND `key` = 'B' I know that won't work, the only way I can think of doing this is joining it on itself: SELECT a.`objec...

MySQL Issue Copying from one table into another directly

I use the following code to copy from one table directly into another: $transfer = $db->exec("INSERT INTO table2 SELECT * FROM table1 WHERE groupname = '$gname'"); The issue I have, however, is the ID field of both tables do not necessarily match (both auto-increment) and at times this may mean one temporary's tables ID# is higher than...

mysql inner join on self, matching previous record

Hello, I have a database of transactions, mapped by a parameter called flaglessContractID. Among those, there are cp_flag which may be either 'ca' or 'pu'. I am trying to get in 1 query a result set which contains on a row a transaction price (PRC_O and PRC_C) and also the previous transaction on that contract (flaglessContractID is the ...

mySQL Syntax Error

INSERT into error_log (id_user, id_error, severity, date) VALUES ('93, '1', '6', '1285886665') Throwing 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 '1', '6', '1285886794')' at line 4 Table CREATE TABLE `error_log` ( `id` int(25) N...

Mysql slow query: JOIN + multiple WHERES + ORDER BY

Hello all, long time lurker, first question! I am struggling to optimize this query, which selects the lowest priced items that match the chosen filters: SELECT product_info.*, MIN(product_all.sale_price) as sale_price, product_all.buy_link FROM product_info NATURAL JOIN (SELECT * FROM product_all WHERE product_all.date = '2010-09-30')...

Can this be written without using a view and instead use a subquery or something

CREATE VIEW myView AS SELECT * FROM `ce_userbadges` WHERE ceub_userid =85; SELECT * FROM ce_badge LEFT JOIN myView ON ceb_id = ceub_badgeid; So it has to select all badges user 85 has and also the badges they dont have (hence the left join). I tryed this but it does the userid filtering after the join SELECT * FROM ce_userbadges r...

mysql to csv without using INTO OUTFILE of mysql

Hi there, I need to export data from mysql to a csv file with column heading but i dont have file permission on the server. Is there any otherway to do it? i.e. using php fwrite? or fputcsv? Any help will be much appriciate. Thanks. ...

Can items with most common descendants be found using SQL / MySQL?

Can SQL be used to find all the brands that has the most common categories? For example, the brand "Dove" can have category of Soap, Skin Care, Shampoo It is to find all the brands that has the most matching categories, in other words, the most similar brands. It can be done programmatically using Ruby or PHP: just take a brand, and lo...

how can I use loop 'for' through a table in SQL to check if a column is 0?

how can I use loop 'for' through a table in SQL to check if a column is 0? I need to do like this in a stored procedure: for each record in tablex if table.column1=0 then insert into table1; Else insert into table2; End for; ...

update with join statement mysql?

Is this possible in mysql? update table1 set column1 = (select column1 from table2 inner join table3 where table2.column5 = table3.column6); Here is a a similar question for an Oracle DB. ...

SQL Multiple Result question....

Can I use a single SQL statement to return three separate results I can display through PHP? I'm looking to formulate three different "total" counts on a single table with three different conditions. Eg something like.... $resultGetTotals = SELECT COUNT(Total1) FROM table_xyz WHERE Status = X AS Total1 SELECT COUNT(Total2) FROM table_x...

Problem with mysqldump: "--defaults-extra-file" option is not working as expected

I run the following command from the Windows command line to backup my database: ...\right_path\mysqldump --add-drop-database --databases my_database_name --defaults-extra-file=d:\1.cnf where d:\1.cnf contains the following: [client] user="my_user" password="my_password" Unfortunately, I got the following e...

SQL Server Running PHP

Best tutorial around for understanding how to set up a separate SQL server besides my regular web server? Want to run an application with a huge DB. ...

Can we add MySQL Indexes without the server and client?

I was given access to a cluster today along with a front-end. The person who gave me access tells me I cannot start anything on the front-end and that I should submit everything as a job. Now I have no idea what that means but I'm thinking that I am not supposed to start MySQL on the front-end. If that is the case, how can I even use the...