mysql

Mysqli query with a SET variable statment (ie multiple queries)

I want to set a variable to use in a mysqli query. This doesn't quite work. Prior to mysqli I used to set query calls. I played around with db->multi_query($sql) with no luck. Anyone out there have an idea how to make this work including a set statement? $sql = 'SET @rownum := 0;'; $sql .= 'SELECT @rownum :=@rownum + 1 AS Rank, User...

What format should my site-map be in?

I am creating a site-map with all urls on my site! I have a classifieds site, so I'm using a mysql database. What format should I create the 'static' page which contains all the urls and is 'auto incremented' for each ad created on my site ? Thanks ...

mysqldumpslow @ Freebsd

I have installed mysql-client-5.4.2 Multithreaded SQL database (client) mysql-server-5.4.2 Multithreaded SQL database (server) on my FreeBSD 8.0 box Why isnt there mysqldumpslow command available? What port should i install to make it available ...

Optimizing MySQL inserts to handle a data stream

Hi, I am consuming a high rate data stream and doing the following steps to store data in a MySQL database. For each new arriving item. (1) Parse incoming item. (2) Execute several "INSERT ... ON DUPLICATE KEY UPDATE" I have used INSERT ... ON DUPLICATE KEY UPDATE to eliminate one additional round-trip to the database. While trying...

Question on xml site map in combination with mod_rewrite !

I have a classifieds database which is displayed to users depending on their query, in a php file! I'm currently struggling (and learning alot) with creating a xml site map. Also, users in the future doing the same thing will definetely find my posts and your help very useful as there is very little info about this out there (atleast th...

mysql import on windows

I have a file, db.sql. I have tried to import it using: mysql -uroot -p[password] db < db.sql All I get is a listing of mysql commands, or I get a syntax error. The weird thing is I used this file last week and, as far as I know, I'm doing it the same way. I create the database, then in command line enter the above but it's not workin...

SQL: Is "LIMIT 1" recommended for query where WHERE condition is based on PK ?

I am querying a mySQL database to retrieve the data from 1 particular row. I'm using the table primary key as the WHERE constraint parameter. E.g. SELECT name FROM users WHERE userid = 4 The userid column is the primary key of the table. Is it good practice to use LIMIT 1 on the end of that mySQL statement? Or are there any speed ...

Need a little clarification on MySQL Indexes

I've been thinking about my database indexes lately, in the past I just kind of non-chalantly threw them in as an afterthought, and never really put much thought into if they are correct or even helping. I've read conflicting information, some say that more indexes are better and others that too many indexes are bad, so I'm hoping to g...

AS3 and mySQL or XML: Saving user votes on server from a flash movie.

Hi, A project of mine involves a flash movie (.swf) in a webpage, where the user has to pick from a number of items, and has the option to thumbs up or thumbs down (vote on) each item. So far I have gotten this to work during each run of the application, as it is currently loading the data from an XML file - and the data is still stati...

Deleting records from the server after 15 days

I need to delete uploaded files from the server after 15 days using SQL. How would I do that? The following is the current code, but it isn't working. <?php $con = mysql_connect("localhost","mt","mt"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("mt", $con); function curdate(){ date_default_timezo...

in this case Csv or Mysql?

I am starting new project. In my project I will need to use local provinces and local city names. I do not want to have many mysql tables unless I have to have or csv is fast. For province-city case I am not sure which one to use. I have job announcements related with cities, provinces. For Csv case I will keep the name of city in anno...

automatic unzipping rar files in pages

Hi frnds i am uploading the files suppose if i download one file from my webpage that should automatically unzip and go to table how i can do that can anyone help me out thanks in advance ...

Skip error while bulk inserting.

I am using following query to bulk insert from one table to another. INSERT INTO billitems SELECT * FROM billitems_old; I want that if insert fails on any row, it must skip that row and proceed. Is there anything that I can include in the above query to skip errors. ...

Multiple select statements in Single query

I am generating a report in php (mysql), ex: `select count(id) as tot_user from user_table select count(id) as tot_cat from cat_table select count(id) as tot_course from course_table` Like this I have 12 tables. Can i make it in single query. If i did? Process gets slow? ...

Need help with simple mysql triggers

Hi, I have one table (TABLE A) in my mysql database which has the listed fields: row1_id - PK row2_id - PK row3_data What I would like to do is, every time a record is inserted or updated in table A, I would like to call a trigger to write me the new information of table A in another table, TABLE A_LOG, which has the following field...

MySQL: several parents identifying their children from one table

I am designing a data model for tourism-site. I have a table for places (countries, resorts, hotels) and a table for tours. The tables are pretty different in fields so the cant be merged into one. Both of them have usual auto_increment as id. Places and tours have photos so there is a third table for photos. This table has 'parent' fiel...

Most efficient way to insert two rows that depend on each other.

I have a site that is essentially a collection of links. A mysql database which stores users, links and votes. My links table has two foreign keys, user_id and vote_id. When a link is inserted into the database it will start with one vote so that means I need to insert a row into the votes table but they essentially depend on one anot...

Numbering Items in Lists - The Best Approach?

I'm creating a simple set of tables to store a number of lists. The first table, describes the lists that I have and second describes items on those lists - the tables are defined like so: CREATE TABLE `listman_list` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `title` varchar(100) NOT NULL, `description` varchar(1000...

Improving search performance

In one of my MySQL tables, I have following columns: Skills varchar(80) Industry varchar(40) Address varchar(100) Skills might include text like: C/C++, MS Office, Linux etc. Industry might include text like: Finance, IT, etc. Address contains complete postal address along with city name. There is no separate city column....

paging next and previous links don't work!

hello can anyone help me with this code?the next and previous links don't work $limit=20; mysql_connect("localhost","root",""); mysql_select_db("autoschool") or die("Unable to select database"); $query = "select * from student"; $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); if ($numrows == 0) { echo "empty"; } ...