mysql

How can I speed up a MySQL product search result?

I am doing a query on an mysql database based on typed data sent from a jquery autocomplete function. It works fine but take several seconds (5-6+) to get the returned suggestions. There are around 200,000 product entries in the database currently which is expected to go to well over a million entries. How can I speed this thing up, as o...

Adding Auto Increment to a column without rebuilding the table

Is there a way to add Auto Increment to an existing column without having mysql rebuild the entire table? Edit - I am doing an ALTER, but it takes ages and ages, because mysql rebuilds the entire table structure. What this question is about is whether there is a way to speedup this process - after all, the logical operation of adding "a...

fetching data on the iphone

Hey i am developing an iPhone app of a existing PhP mySQL website. i want to know, how to pull data from database and display on the phone? after going through several articles , i found out two ways.. 1) create a web service, on server end, which will provide all the data in XML format to the device. 2) export php,mysql data in JSON f...

PHP while loop omits first entry from MySQL database select

I have four rows in my table. Only three are shown. $query = "SELECT * FROM table"; $result = mysql_query($query); $row = mysql_fetch_array($result); while($row = mysql_fetch_array($result)) { echo $row['id']; } The result is 234, but should be 1234. What am I doing wrong? ...

MySQL - Getting information from bus routes database

Hi, The problem I'm having is with fetching data from the following tables where a bus route contains 1 change or more. I am still relatively new to SQL. I have a database containing 2 tables (bus_route and bus_stop). bus_route contains the following columns: route - the number of the bus run - the direction of the bus (1 or 2) sequen...

In server the site is not loading which is developed using PHP and MYSQL and with smarty Framework

Hi all, I developed a project using PHP as FRONT END and MYSQL as BACKEND using SMARTY FRAMEWORK. In my localhost the site is working perfectly. But in server it doesn't load. Here is the URL : http://utovglobal.com/new_mandapam/ I give permissions to all the folders. Even it didn't work. Any suggestions or help will be thankful an...

Security Exception while using MYSQL dll on godaddy

Hi, I have hosted my website on godaddy and referenced MYSQL.Data.dll. This works fine on localhost. However when i try to run the same code online it throws the following error. System.Security.SecurityException: That assembly does not allow partially trusted callers. I have downloaded the MqSQL.Data code version 6.2.4. which already ...

Installing Joomla on Ubuntu

I am trying to locally install joomla on my laptop. I have apache2 installed and working. When I go to localhost:8080 I get to the Joomla's install page. This is what I see PHP Version >= 4.3.10 Yes - Zlib Compression Support Yes - XML Support Yes - MySQL Support No MB Language is Default Yes MB String Overload ...

Why is java.sql.DriverManager.getConnection(...) hanging?

I am attempting to get a connection to my University's MySQL DB but the connection is hanging. import java.sql.*; public class ConnectToDB { public static void main(String args[]){ try { Class.forName("com.mysql.jdbc.Driver").newInstance(); String url = "jdbc:my...

mysql export loses AutoIncrement

When I export a table through SQLYog, the CREATE statement in the exported file lacks AutoIncrement on its primary key, even though the original table contained AutoIncrement. Is this a bug (in Yog or in mysql?)? Should I report it? How can I export the table with the AutoIncrement in place? ...

Variable I need to use in other classes

Hello all. The following code connects my database var $connection; function MySQLDB(){ $this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error()); mysql_select_db(DB_NAME, $this->connection) or die(mysql_error()); etc etc This code is within a class called MySQLDB At the end $database = new MySQLDB;...

PHP MySQLi Stmt Prepare

How would I mysqli::stmt->bind_param something that's considered as NULL in mysql? I'm currently using $stmt->bind_param('s', 'NULL'); ...

For a beginner, is there much difference between MySQL and PostgreSQL

IS there much difference between MySQL and PostgreSQL for a beginner like me, using basic select statements and the like, or are the main differences with using more advanced queries? ...

Mysql REGEXP 'o' OR 'aw'

I'm trying to do a friend search app. So to do that I'm using regular expressions like: SELECT ... WHERE firstname REGEXP $firstname And the $firstname variable is a string - for example: (ch|k)ris But for some reason it does not work. maybe the ( | ) doesn't work in MySQL? What can I use instead of that operator to do the same ...

MySQL table structure question.

I was wondering how should my categories mysql table look like and which INDEX or KEY is correct or are all four correct? INDEX (parent_id) INDEX parent (parent_id) INDEX parent_id (parent_id) KEY parent_id (parent_id) Here is my MySQL code. CREATE TABLE categories ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INT UNSIGNE...

How Database stores data internally in B-Tree/B+Tree

My question is that How database stores data and how it performs query internally. Suppose we have following fields in our table: ID Name Age Weight Manager and we query select * from Table1 where age>50 and weight<100 I am just curious that how it perform query internally. What will the Node of B-Tre/B+Tree contains in this exam...

PHP: filter same column two times in query

$result = mysql_query("SELECT * FROM users_msgs WHERE uID = '$USER' AND date<'$lastmsg' AND date BETWEEN $Yday AND $today ORDER by date DESC LIMIT 10"); Im getting 0 rows while there should be 1.. But my other query, $result = mysql_query("SELECT * FROM users_msgs WHERE uID = '$USER' AND date > $today AND date<'$lastmsg' ORDER by ...

Generate Alphanumeric ID from Numeric ID in MySQL/PHP

Hi, I have a basic MySQL database where each row is assigned a unique incrementing ID number, however I would also like each row to have a unique 6 character, alpha numeric ID which also increments. Example: Row 1: ID = 1, Name = AAAAAA Row 2: ID = 2, Name = AAAAAB Row 3: ID = 3, Name = AAAAAC The database is added to via PHP so this...

Rails3 ActiveRecords displaying column with wrong datatype?

I am not sure how to title this question, its something rather weird. Well, here is the thing, I've created a model called UserProfiles for which the migration file looks like this: class CreateUserProfiles < ActiveRecord::Migration def self.up create_table :user_profiles, :primary_key => :id, :options => "auto_increment = 1" d...

MySQL - Correct way to use the UNIQUE KEY?

Is the proper way to use the UNIQUE KEY in my MySQL table? MySQL data. id pid aid 1 2 3 2 3 2 3 3 4 MySQL table. CREATE TABLE ab ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, pid INT UNSIGNED NOT NULL DEFAULT 0, aid INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY (pid, aid) ); ...