mysql

sql 3NF Normalization

is this in 3NF ? create table movies( id numeric primary key not null default autoincrement, name varchar(50) not null, release-date Date, price numeric, rating numeric, length numeric, description varchar(1500) ); create table movies( id numeric primary key, name varchar(20) ); create table genre( name varc...

mysql_fetch_array problem code not processing

My page stops loaded every time I turn on the code below... it looks correct and the tables and fields are correct. <select name="common" style="width: 136px;"> <?php $group1 = mysql_fetch_array(mysql_query("SELECT country FROM lang_list WHERE grouping = '1' ORDER BY p_order")); while($row = $group1){ echo "<option ...

Need to optimize this query

I have a task to optimize this query, any idea will be appreciated. SELECT DISTINCT `br`.`id` AS `branch_id` , `br`.`store_id` , `br`.`longitude` , `br`.`latitude` , `br`.`town_id` , `br`.`post_region` , `cb`.`cat0_id` , `cb`.`cat1_id` , `cb`.`cat2_id` , `plan`.`listing_pla...

Mysql: How to find all items in table A which are not in table B for memory tables

I have temporary memory tables A and B. each contain 1 column of integer values I need to find all values which are in A but not in B. The problem is that its very slow due to the fact (I think) that memory tables use hash and not ordered keys. How can I perform it efficiently? Currently I'm using SELECT val FROM tableA WHERE val NOT ...

Display in range addresses within x miles of a geo location with google maps

In my database I have a list of places and for each I have a street name and number, postcode, city and county. Some of them have a latitude and longitude location. And I have the geo location of the city centre for example. I would like to display only the places that are within X miles of the city centre on a google map. Incase this ...

php greek characters encoding

I have a url string which i encode to utf8 at client side. When data recieved in server with my php script i can not see greek language characters! Could you please help me to convert them? data will be saved in mysql database . ...

mysql SQL_CALC_FOUND_ROWS problem with join

Hi Why this query returning 0 rows? SELECT SQL_CALC_FOUND_ROWS a.*, b.id AS key1, b.name AS catname FROM #__cd_ads a LEFT JOIN #__cd_categories b ON a.type = b.id when removed b.id AS key1, b.name AS catname than works. ...

How to copy a table from one mysql database to another mysql database

Hi, I need to copy a table from one database to another. This will be a cronjob. Which one is the best way to do it? PHP script or Shell Script. The problem with PHP, both databases has different usernames and passwords so I can't do it like this. CREATE TABLE db1.table1 SELECT * FROM db2.table1 Should I just connect first DB get all r...

MySQL coding problem.

How can I join users with users_friends.user_id when user_id = '" . $user_id . "' And join users with users_friends.friend_id when friend_id = '" . $user_id . "' if user_id = '" . $user_id . "' INNER JOIN users ON users_friends.user_id = users.user_id if friend_id = '" . $user_id . "' INNER JOIN users ON users_friends.friend_id = u...

MySQL Fulltext search -- multiple tables

I've currently got 3 tables (neighborhoods, cities, states) and I'm trying to list the top 10 best matches (if not an actual match) for a given search term. What I need to do is have it so if there is a neighborhood name match, have it rank higher than a city name match. What confuses me is that if it finds a neighborhood, I'd need it to...

How to Notice Close of MySql Server in Qt

When I closed MySql server, how can I understand that mysql server is gone away from my Qt program? Edit: Here my trial: When I close MySql, I get these results, and I can't catch that MySql is closed. My Code Snippet is QSqlQuery query(db); query.exec("SELECT * From RequestIds"); qDebug()<<query.lastError(); qDebug()<<db.lastError(...

Database design - primary key naming conventions

Hi, I am interested to know what people think about (AND WHY) the following 3 different conventions for naming database table primary keys in MySQL? -Example 1- Table name: User, Primary key column name: user_id -Example 2- Table name: User, Primary key column name: id -Example 3- Table name: User, Primary key column name: pk_us...

Has PHP a function to ENCODE a MySQL Table into XML like ...?

<?xml> <table> <row> <columnA>ValueA</columnA> <columnB>ValueB</columnB> <columnC>ValueC</columnC> <columnD>ValueD</columnD> <columnE>ValueE</columnE> </row> <row> <columnA>ValueA</columnA> <columnB>ValueB</columnB> <columnC>ValueC</columnC> <columnD>Valu...

Error installing mysql2: Failed to build gem native extension.

Windowsxp. Ruby192. Rails installed. Mysql installed. When I try to run gem install mysql2 (or bundle install) I get errors: C:\ruby\cred2>gem install mysql2 Building native extensions. This could take a while... ERROR: Error installing mysql2: ERROR: Failed to build gem native extension. C:/Ruby192/bin/ruby.exe extconf.rb ch...

Django: most efficient way to query many records?

Hello, I have a table with a few thousands records (products). Each product has a 4 different categories: CAT1 CAT2 CAT3 CAT4 I wonder if is there a method, or what is the best practice, to dynamically retrive the available categories based on the categories already selected (using Ajax). Example: if CAT1 = green all the products wit...

php mysql select dropdown selected and distinct query

Hello, I am confused here. $sql_n = mysql_query("SELECT * FROM table1 WHERE n='{$row['n']}'"); $row_n = mysql_fetch_array($sql_n); $sql= mysql_query("SELECT DISTINCT p FROM table1"); while($row = mysql_fetch_array($sql)) { if($row['p'] == $row_n['p']) { $selected = " selected"; } $np .= "<option value='{$row['p...

mySQL table relations or just use one table?! Need Help php mysql

I'm trying to create a drop down select option using PHP, MySQL, and ajadx I have the PHP and ajax pretty much figured out but I'm stumped on how I should organize my tables I couldn't decide so I created it both ways: The first way the tables are: CourseTbl containing (CourseID*, and CourseName) DateTbl, (DateID*, and DatesOffered(en...

Importing Excel Sheets into MySQL with Values that relate to a separate table

Hello everyone, First up, this might be the wrong place to ask this question.. So, sincere apologies for that! I have two MySQL Tables as follows: First Table: employee_data id name address phone_no 1 Mark Some Street 647-981-1512 2 Adam Some Street 647-981-1214 3 John So...

Two-way encryption on a local webserver that could get stolen

I have an offline kiosk computer that will be running a LAMP web server and hosting a form for people to walk up and fill out. The data they submit will be encrypted and stored in a MySQL database (all stored locally on that machine). The concern is that if the entire box was stolen, someone would potentially be able to get into the cod...

MySQL Insert Race Condition

I have a webapp that currently stores all of a user's searches into a search_log table. I now want to create another table called results_log that stores all the results we supply to the user. The search_log table contains a primary key called id_search and the results log table has the foreign key id_search, and one other field id_res...