mysql

Getting a MySQL database difference

I have a mysql database. What I'd like to do is perform an arbitrary action on it, and then figure out what changed. Something like this: //assume connection to db already established before();//saves db state perform_action();//does stuff to db diff();//prints what happened I'd want it to output something like: Row added in table_0...

MySQL fulltext search on multiple tables with different fields

I am trying to join multiple tables and perform a full text search on them. Most of the tables are unrelated but have similar fields. I have had the fulltext searches working but i need to be able to create links from the results which is the next step but i don't thin k it will work becuase i haven't got enoygh fields to get enough i...

What does "#" signify in MySql?

I come from a SQL Server background and thought that it might mean it was a temporary table, but after reading up on MySql temp tables I don't think that's true. I'm seeing it in the following context: SELECT ID, Name FROM #_SomeName UPDATE This query is defined in a PHP string and then run against the MySQL Database. I'm not sure ...

MySQL import records and shift primary key increment

I have a MySQL table that contains the columns id, created_date, and name, where id is the auto-incremented primary key (starting at 1) and created_date is the date the row was created (just an example). There are 50 rows in this table since the launch of this application. Suppose I have 100 old records (considering the created_date val...

Progressive count using a query ?

I use this query to SELECT userId, submDate, COUNT(submId) AS nSubms FROM submissions GROUP BY userId, submDate ORDER BY userId, submDate obtain the total number of submissions per user per date. However I need to have the progressive count for every user so I can see how their submissions accumulate over time. Is this possible to i...

PHP MySQL connection

I'm been looking through the Internet for an answer for a long time. How could I control users and connections between php and MySQL. My question is, when we create a mysql database through php, actually we create the user in a user table in mysql. When I connect to the database, all the user we created is using same mysql user account t...

Can I use non-aggregate columns with group by?

You cannot (should not) put non-aggregates in the SELECT line of a GROUP BY query. I would however like access the one of the non-aggregates associated with the max. In plain english, I want a table with the oldest id of each kind. CREATE TABLE stuff ( id int, kind int, age int ); This query gives me the information I'm aft...

Why have a resource in mysql_real_escape_string?

I've been wondering for the longest time WHY I actually need a live resource to SQL connected in order to use mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier ] ) Does this function not simply escape the data? What's the point of connecting? I want to use the function without a connection, I'm debating cr...

Can a table field contain a hyphen?

I have a table in a MySQL table with a fieldname 'product', and want to rename it to 'ds-product'. The CMS type system I am using uses the id of formfields as the name of the table field to insert into. For most this works fine, but for a particular field it prepends 'ds-' to whatever ID I give it, so I must make the table field name m...

How to add a LEFT JOIN to this MySQL Query?

Hi! I'm quite a beginner at this so please forgive me if this seems a bit easy for some of you. So, I have this query here: SELECT code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.rating AS rating, code.date, code_tags.*, tags.*, ...

Problems storing content from a form via CMS

I have a MySQL table called sales, containing the fields product, quantity and paid. I am using a CMS like system which has a custom way of doing forms, and custom formfields to use. As far as I can tell, the id given to a formfield is what is used as the table name to insert into. My current form I am using is as follows: <?php ...

MySQL string split

I uploaded a csv file to MySQL DB but for some reason data from two columns got glued to one. e.g. the data 0880174V,D should have gone to two different columns. Is there a way (via sql) i can split the data from this column (i.e. as 0880174V and D) and update the table, without having to delete the record and re-upload? I have a milli...

How to improve this mysql query in speed .

here it is SELECT tbl_rls . * , ( SELECT count( * ) FROM `comments` WHERE `post_id` = `tbl_rls`.`id` ) AS `t_comments` FROM `tbl_rls` WHERE 1 =1 AND `status` <> 'denied' AND ( `id` IN ( SELECT `rls_id` FROM `tbl_visitors_logs` WHERE `date` LIKE '2010-07-02%' AND `page_type` = 'post' GROUP BY `rls_id` ORDER BY count( * ) DESC ) ) AND ...

Making a PHP/MySQL Timestamp look more attractive

Hello there! So basically, I'm currently selecting a Timestamp from my MySQL database. In the database, the timestamp looks like so: 2010-06-30 12:36:08 Obviously for a webapp, that's not very attractive for users to view. So, using some CodeIgniter functions, I made it look a bit nicer. <h4 class="timestamp"> <?php // Quickly c...

Redirecting users when they have made 10+ submissions in one hour

Hello, The code below is supposed to check to see if a user has made an addition to a MySQL database table called "submission" more than 10 times in the last day. If the answer is yes, then the user is re-redirected back to the index page. As far as I can tell, it works just fine. How could I modify to make it do the re-direct if the...

Utility to create an ERD from an already existing MySQL DB?

I have my database built already, but I really want to print out an erd to reference while I'm building my models. Is there a utility that can generate an erd from a SQL dump or by connecting to the database directly? Thanks in advance, ~Brandon ...

mysql wtf with information_schema.tables

-edit2- 3hrs later and still have the same problem. I am using the noinstall archive package. -edit- maybe someone can tell me a better way to check if a table exist? I have a function in my lib to check if a table exist which i asked how to do in the past. I deleted my database and created it again. My code didnt create the tables pro...

MySQL Categories and Subcategories Table Structure

Hi everyone, I am having a little bit of a problem with setting up a mysql table that will hold a list of categories and subcategories. I am not to sure how to setup the table. Does it need to be 2 separate tables? 1 for the main categories and 1 for the subcategories or can it be all in 1 table? Would something like this work? Create T...

Monitor Database Updates Live using AJAX and SQL

How can one trigger an event locally when a field in a connected database changes? I've done some research but it seems like there there are varying and inconclusive answers to the question. I'm building an application where users rate comments, and I'd like to have the ratings change live when they are modified by a different user. For...

Suggestion For Finding Similar Rows In Mysql

Hi, i want select similar rows accourding to row's title columun. Title columun has mostly have 5 or 6 six keywords. Which algorithm do you recommend ? Soundex Maybe ? P.S: Title columun has unicode chracters like Ç, Ö, Ş... ...