mysql

Which one scales better?

I have to make a design decision with regards to fields in the database. Given: users table Problem: Visually we have a check box that says: [*] check this box to not show this again Should this data(boolean) be saved in the users table in a column or in a new joint table that has the user_id? Which one scales better? faster? ...

Mysql - Help me alter this query to apply AND logic instead of OR in searching?

First execute these tables and data dumps :- CREATE TABLE IF NOT EXISTS `Tags` ( `id_tag` int(10) unsigned NOT NULL auto_increment, `tag` varchar(255) default NULL, PRIMARY KEY (`id_tag`), UNIQUE KEY `tag` (`tag`), KEY `id_tag` (`id_tag`), KEY `tag_2` (`tag`), KEY `tag_3` (`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ...

problem during data fetch

here is my code $sql="SELECT * FROM $tbl_name WHERE ownerId='$UserId'"; $result=mysql_query($sql,$link)or die(mysql_error()); $row = mysql_fetch_array($result, MYSQL_ASSOC); <?php while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { ...

How to make xml file from php and mysql

My fellow friend is building site in flash and he uses xml files to access data in Flash. I want to build editable cms so that client can edit stuff. Now i don't' have any experience with XML. I know php html mysql very well. SO how can i change those already build xml files using mysql and php. ANy help?? ...

Does Doctrine 1.2 support importing indexes with Doctrine_Core::generateModelsFromDb function?

Does Doctrine 1.2 support importing indexes with Doctrine_Core::generateModelsFromDb() function? I need to make a migration between 2 database connections, one of them having unique index on 2 fields and one doesn't. And my migration is empty. It looks like Doctrine doesn't support indexes when importing from databae, other than those t...

how to write a bat file to execute the .sql fiel

I want to execute query in side the db.sql file using MySQL 5.1 in windows environment. Can any one help me to this. I want to do this by runing a bat file. ...

PHP/MYSQL Trouble Selecting by Primary Key

Hi all, So I have a primary key column called key. I'm trying to select the row with key = 1 via this code: $query ="SELECT * FROM Bowlers WHERE key = '1'"; $result = mysql_query($query) or die(mysql_error()); For some reason, I'm getting this result: You have an error in your SQL syntax; check the manual that corresponds to your...

MySQL, how to use returned data?

Well, I know there is a funciton mysql_fetch_array() and we can use it like this: while ($row = mysql_fetch_array($result)) { echo $row['name'] . "<br />"; } But is there any other way? For example, if there is only one element that can be returned, and not an array. Thanks) ...

PHP/MYSQL Different Trouble Selecting By Primary Key:

Due to some help from a recent post, I'm selecting a row by primary key, as follows: $query ="SELECT * FROM Bowlers WHERE 'key' = '1'"; $result = mysql_query($query) or die(mysql_error()); $row = mysql_fetch_assoc($result)or die(mysql_error()); For some reason, the third line of code dies every time, without error. It works fine usin...

Error in MySQL Workbench Forward Engineered Stored Procedures

I am using MySQL Workbench (5.1.18 OSS rev 4456) to forward engineer a SQL CREATE script. For every stored procedure, the automatic process outputs something like: DELIMITER // USE DB_Name// DB_Name// DROP procedure IF EXISTS `DB_Name`.`SP_Name` // USE DB_Name// DB_Name// CREATE PROCEDURE `DB_Name`.`SP_Name` (id INT) BEGIN SELECT *...

MYSQL Fast Insert dependent on flag from a seperate table

Hi all. For work I'm dealing with a large database (160 million + rows a year, 10 years of data) and have a quandary; A large percentage of the data we upload is null data and I'd like to stop it from being uploaded. The data in question is spatial in nature, so I have one table like so: idLocations (Auto-increment int, PK) X (float) ...

problem in counting two fields in one query

hey guys i need to count new private messages and old one from a table so first thing come to mind is using mysql_num_rows and easy thing to do // check new pms $user_id = $userinfo['user_id']; $sql = "SELECT author_id FROM bb3privmsgs_to WHERE user_id='$user_id' AND (pm_new='1' OR pm_unread='1')"; $result = $db->sql_qu...

stored procedure with cursor in MySQL

Can I use Cursor with stored procedure in MySQL? And can I receive the output values from it? ...

How do I get all the ids of the row created by one multiple row insert statement.

Hi All. I'm new to php. So, please forgive me if this seems like a dumb question. Say i have a MySQL insert statement insert into table (a,b) values (1,2),(3,4),(5,6). table 'table' has a auto increment field called 'id'. how can I retrieve all the ids created by the insert statement above? It will be great if i get an example that u...

MySql product\tag query optimisation - please help!

Hi There I have an sql query i am struggling to optimise. It basically is used to pull back products for a shopping cart. The products each have tags attached using a many to many table product_tag and also i pull back a store name from a separate store table. Im using group_concat to get a list of tags for the display (this is why i ha...

Load huge sql dump to mySql

Hi I have a huge mysql dump file generated from phpmyAdmin (150 000 lines of create table, inserts....) The mySql query tool fails to open such a big file. As a result i can't insert all the records. Is there a way to do this ? Thanks John ...

mysql query quote signs

Hi guys I am a newbie to mysql. I am confused by the quotes signs. In mysql query Does it make any different between CREATE DATABASE 'mydatabase'; and CREATE DATABSE mydatabase One with quote, the other without Also with Insert and other commands Thanks ...

Making a mySQL-query to get products that have not ben sold?

Using openbravoPOS i got a mySQL database of all my producs tickets stock etc. Products are in one table and the tickets are in another, so to make a query that get me all the products that are sold (and therefore are in ticketlines) are simple. How do I make a query that gives me the products that are not i any ticketlines? ...

Get post author name and category name from other table by ID

I have three mysql tables, first holds information about articles (Article ID, author ID, title and content), second tables holds information about registered users on my site (ID, name, password, email), third holds information about categories (ID, name). I need to get all the articles and displays title, author name and category name...

cakePHP and GROUP BY

I am trying to solve a hopefully simple problem here is the query I am trying produce: SELECT `categories`.*, COUNT(`entities`.id) FROM `categories` LEFT JOIN `entities` ON (`categories`.`id` = `entities`.`category_id`) GROUP BY `categories`.`id` I am really struggling to do this is in cakePHP 1.2 How would/should I go about doing t...