mysql

mysql dynamic cursor

Here is the procedure I wrote- Cursors c1 & c2. c2 is inside c1, I tried declaring c2 below c1 (outside the c1 cursor) but then I is NOT taking the updated value :( Any suggestions to make it working would be helpful, Thanks create table t1(i int); create table t2(i int, j int); insert into t1(i) values(1), (2), (3), (4), (5); inse...

Which method should I go with; Indexing MySQL db with SOLR

I have a classifieds website, with approx 30 categories of classifieds. I am on the stage where I have to build MySQL tables and index them with SOLR. Each row in a table has around 15 fields... I am looking for performance! I wonder which of these two methods works best: 1- Have one MySQL table for each category, meaning 30 tables, ...

PHP select statement from MySql to Excel. Help with formatting date to YYYYMMDD

Note: In the MySql database, "dob" is varchar and in this format: mm/dd/yyyy Date of birth, Cvrg Effective Date, & Cvrg Expiration Date need to be changed from mm/dd/yyyy to yyyymmdd when it arrives in excel. Any help is greatly appreciated. Please see the code below: <?php if($qry == "ok"){ // get db connection inc...

DBD::mysql - Problem with dropping a database

Hello! Why doesn't the line "$rc = $dbh->func( 'dropdb', $dbname, 'admin' );" remove the database db_test_2? #!/usr/bin/perl use warnings; use strict; use 5.010; use DBI; my $host = 'localhost'; my $user = 'user'; my $password = 'password'; my( $rc, $dbname, @databases ); my $drh = DBI->install_driver( 'mysql' ); $dbname = 'db_test_...

Does dropping a SQL table reset its ID value?

Will the ID auto-increment value be reset if I drop (wipe) a MySQL table? And, if I delete (for example) the entry N° 535, will this entry number be filled again later? I don't want that ID to be filled with other new entries if I wiped old data. If this is not the behavior, then what's the solution to avoid this? ...

HTAccess - Confusing clean url's

Hey, If i wanted to simply redirect /clients/page/ to /clients.php?view=page i would use something as simple as this, it works great. Options +FollowSymlinks RewriteEngine on RewriteRule ^clients/([^/]+) /clients.php?section=$1&$2 [NC] If i had for example, a url like: /clients/page/?name=Tom how could i transfer those HTTP GET varia...

how to reindex mysql table

I have a table with many rows but they are out of order. Im using the field "id" as the primary key. I also have a "date" field which is a datetime field. How could i reindex the table so that the entries are id'd in chronological order according to the date field ...

Foreign Keys, question on relations

I've been toying with MySQL today and reading their documentation, gotten some handy information of optimization and a lot of things I didn't know. Now I've been adding foreign keys to my web application because it add constrains and I see it quite helpful. My doubt is, right now there is a Roles table and a Users table, I set a relati...

MySQL incorrect key file for tmp table when making multiple joins

I don't come here for help often but I am pretty frustrated by this and I am hoping someone has encountered it before. Whenever I try to fetch records from a table using more than one join I get this error: #126 - Incorrect key file for table '/tmp/#sql_64d_0.MYI'; try to repair it So this query will produce the error: SELECT * FROM...

Slow MySQL Query Breaking my back!

so, I have tried everything I can think of, and can't get this query to happen in less than 3 seconds on my local server. I know the problem has to do with the OR referencing both the owner_id and the person_id. if I run one or the other it happens instantly, but together with an or I can't seem to make it work - I looked into rewriting ...

MySQL: Query to get all rows from previous month

Hi All, I need to select all rows in my database that were created last month. For example, if the current month is January, then I want to return all rows that were created in December, if the month is February, then I want to return all rows that were created in January. I have a date_created column in my database that lists the dat...

MySQL progressively decaying...?

I have a Zend/PHP script that reads rows from a table in one MySQL DB, transforms the data, and adds rows to a second table in another MySQL DB. As I've bee debugging the script, it has been getting less and less far along before tossing an error. Right now, after adding 60 rows, it quits. In the beginning it was adding 300+ rows. The s...

Select next row in php

Hello, I have a set of records in a mysql database and am trying to make select next and previous buttons on a webpage. I have issues coding it in php however; any help is greatly appreciated. My records do not have auto increment id attached to them. My code is as follows: $result = mysql_query($query) or die("Couldn't execute query...

What's the best way to update n items in relation to one item?

I apologize that the title may not make sense, so let me describe my problem. I have a table for Questions, and a table for Answers. One Question has many Answers. When I create a Question, I simply INSERT as many Answers as were provided. The problem comes into play when I have to update the Answers (either adding new ones, editing exis...

MySQL @variable

I'm trying to set up a MySQL trigger, but I can't figure out how exactly to get what I want done. I think I need to set up a MySQL @variable but what I've tried hasn't worked and I have not been able to find a good resource figure it out. I change the delimiter in phpMyAdmin, and the comment in the below is not used in the actual query. ...

MySQL: Replace substring if string ends in jpg, gif or png

Hi gang, I'm doing a favor for a friend, getting him off of Blogger and onto a hosted WordPress blog. The big problem is, with over 1,800 posts, there are a lot of image links to deal with. WordPress has no mechanism to import these automatically, so I'm doing it manually. I've used wget to download every single image that has ever be...

how to handle deletion of many objects from a certain table

We have a user table which contains all the users from the company. As an administrator you have the permission to delete user entries. The UI shows all the users with a checkbox per row to indicate if it needs to be deleted. It's quite possible that other tables have foreign key relationship(s) with the user table and hence would preven...

PHP's database operation not working property in IIS?

After this statement: insert into table... value(..,"It\'s my title") In database I can see : It\'s my title It only happens when in IIS.How to fix? ...

where phpmyadmin store database files?

I have uninstall wamp server and now I need my database to restore. How can I do this process? ...

Approach to sending alerts with CakePHP

This is a rather obscure question so please bare with me. It's more about approach than syntax. I have a MySQL table filled with 'notifications' (id,user_id,date,etc). I have to send an alert (via email, facebook, twitter, whatever... not the issue) when each of those entries pings as 'true'. Here's the thing, how should I go about ping...