mysql

In mySQL, Is it possible to SELECT from two tables and merge the columns?

If I have two tables in mysql that have similar columns... TABLEA id name somefield1 TABLEB id name somefield1 somefield2 How do I structure a SELECT statement so that I can SELECT from both tables simultaneously, and have the result sets merged for the columns that are the same? So for example, I am hoping to do some...

MySQL: Return grouped fields where the group is not empty, efficiently

In one statement I'm trying to group rows of one table by joining to another table. I want to only get grouped rows where their grouped result is not empty. Ex. Items and Categories SELECT Category.id FROM Item, Category WHERE Category.id = Item.categoryId GROUP BY Category.id HAVING COUNT(Item.id) > 0 The above query gives me the re...

Problem converting php code for mysql to sqlite

I am converting my php code for MySQL to SQLite. I am new to SQLite. Kindly help me convert this portion. if (mysqli_fetch_row($result)== NULL){ echo "Inserting new record <br />"; $stmt = mysqli_prepare($con,"INSERT INTO received_queries VALUES (?,?,?,?)"); mysqli_stmt_b...

how to decrease queries in php/mysql array selection loop

hey guys i need to show stories details and tags' names in my php/mysql project . for every story row, there is a filed named : tags that save tags id as an array Table name: stories table filed : tags example of tags filed : 1 5 6 space between them and i have a tag table that looks like this Table name : ...

MySql: make this query faster... is there a way ?

There are 4 tables: Books : id, name, author, ecc... Category : id, name Library : id, name, street, city, ecc.. bookcorr : book_id, category_id, library_id Ids are all keys. The query must show the categories with the numbers of books in a defined Library. for ex: Library X: Romantic (50) Yellow (40) Scien...

Best way to install multiple versions of Apache, PHP and MySQL on a single FreeBSD host

I want a test- and development-environment for web using Apache, PHP and MySQL. I need to be able to test a single web-application with multiple versions of PHP (5.2, 5.3, etc) and multiple versions of MySQL (5.0, 5.1, 5.5, etc). It shall be hosted on a FreeBSD server. My idea is to compile each version into a directory structure and ru...

Mysql-how to update the "domain.com" in "[email protected]"

Hi there, In my database I have a lot of users who've misspelled their e-mail address. This in turn causes my postfix to bounce a lot of mails when sending the newsletter. Forms include (but are not limited to) "yaho.com", "yahho .com" etc. Very annoying! So i have been trying to update those record to the correct value. After executin...

Parsing CSV File to MySQL DB in PHP

I have a some 350-lined CSV File with all sorts of vendors that fall into Clothes, Tools, Entertainment, etc.. categories. Using the following code I have been able to print out my CSV File. <?php $fp = fopen('promo_catalog_expanded.csv', 'r'); echo '<tr><td>'; echo implode('</td><td>', fgetcsv($fp, 4096, ',')); echo ...

How do you design a database to allow fast multicolumn searching?

I am creating a real estate search from RETS data using MySQL, but this is a general question. When you have a variety of columns that you would like the user to be able to filter their search result by, how do you optimize this? For example, http://www.charlestonrealestateguide.com/listings.php has 16 or so optional filters. Granted, ...

MySql: make this query faster… is there a way ? PART TWO

This is part two of the question: http://stackoverflow.com/questions/2913639/mysql-make-this-query-faster-theres-a-way this query still run slowly: SELECT b.id, b.name, c.name FROM bookcorr as a JOIN books as b on b.id = a.books_id = JOIN Library as c on c.id = a.library_id WHERE a.category_id = '2521' AND a.library_id ...

summing two colums total in mysql

I want a resultset for this table : ID Number_of_posts Number_of_user 1 100 21 2 23 34 as ID Number_of_posts Number_of_user Number_of_posts_AND_Number_of_user 1 100 21 178 2 23 34 178 -------------------------------------...

PHP coding a price comparaison tool

Hello, it's the first time I developp such tool you all know (the possibility to compare articles according to price and/or options) Since I never did that i want to tell me what do you think of the way i see that : On the database we would have : offer / price / option 1 / option 2 / option 3 / IDseller / IDoffer best buy / 15$ / f...

MySQL + InnoDB table size question

Hello, I have a simple test table. I'm trying to figure out storage requirements for different storage engines. I have this table: CREATE TABLE `mytest` ( `num1` int(10) unsigned NOT NULL, KEY `key1` (`num1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; When I insert some values, and then run show table status; I get the following...

Summarize Data with SQL statement

I have CMS table that contain CMS_STARTTIME and CMS_STOPTIME (attachment). I want to create report to summarize exist data by using Date, Time and Year as Parameter (attachment). I don't know how to create sql statement to meet this requirement. I'm using MySQL and MS-SQL. Does one can help? Thank you very much. ...

php reading mysql bit field returning weird character

I am using mysql_fetch_assoc($query), one of the bit field returns out to be , which is supposedly to be true. The problem is that I also need to output this to xml and it's an illegal xml character. the charset for the db table is utf-8. why does this happen? ...

MySQL - AFTER INSERT TRIGGER does not run UPDATE query?

Using MySQL 5.1.x Trying to add a trigger to a table: DELIMITER $$ CREATE TRIGGER group AFTER INSERT ON dataTable FOR EACH ROW BEGIN UPDATE dataTable SET groupName = mid(longName,1,4) WHERE groupNAME IS NULL; END$$ When I insert a record there is no update made. Is there a syntax error? Or can I not run the Update query on the aft...

Questions and considerations to ask client for designing a database

Hi guys! so as title says, I would like to hear your advices what are the most important questions to consider and ask end-users before designing database for their application. We are to make database-oriented app, with special attenion to pay on db security (access control, encryption, integrity, backups)... Database will also keep s...

What are the downsides of a text-based primary index

I am working on a website and I want to make pretty urls. The urls are for certain extensions I am going to make. For example I want to have a url like this: http://www.mydomain.com/extensions/tester http://www.mydomain.com/extensions/worker http://www.mydomain.com/extensions/this-is-a-really-long-ext-name So the tester, worker, etc ar...

MySQL foreign key constraints, cascade delete

Hello. I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB). How do I make a SQL statment that DELETE ON CASCADE? If I delete a category then how do I make sure that it would not delete products that also are related to other categories. The pivot table "categories_products" creates a many-to-many...

Drupal 6: moving localhost to server | clear cached data

Hi friends, I worked on localhost to build my drupal site. then, I moved to server. Steps: Clear cache tables (Site Configuration > Performance > Clear Cached Data button) on localhost Export db and then import on live server Move files/folders to live server Edit settings.php to reflect live server config everything is working gr...