mysql

MySQL comparisons between multiple rows

I have a MySQL table with the following columns: id(int), date (timestamp), starttime(varchar), endtime(varchar), ... I need to find time slots that are occupied by two or more rows. Here is an example table id| date |starttime|endtime | __|_____________________|_________|________| 1 | 2010-02-16 17:37:36 |14:35:00 |17:...

Adding a Third Table to a Two-Table Join Query

Hello, The query below works just fine. It pulls fields from two MySQL tables, "comment" and "login". It does this for rows where "username" in the table "login" equals the variable "$profile." It also pulls fields for rows where "loginid" in the table "comment" equals the "loginid" that is also being pulled from "login." I would li...

Mysql: Order Results by number of matching rows in Second Table.

I'm not sure the best way to word this question so bear with me. Table A has following columns: id name description Table B has the following columns: id a_id(foreign key to Table A) ip_address date Basically Table B contains a row for each time a user views a row from Table A. My question is how do I sort Table A results, base...

PHP mySQL Error

Hello, Im new to php so I decided to follow this tutorial for a simple login screen. I got the code setup but when I try login I get this error: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in (a long file path to the script) on line 27 The code I got from the tutorial is: <?php ob_start(); $hos...

How to read large number of rows efficiently using Zend_Db?

Is there a simple :) and efficient way or reading very large number of rows sequentially using Zend_Db? Basically I need to process entire table, row by row. Table is large, primary key sequence is not guaranteed(i.e. not an autoincrement, but is UNSIGNED INT). What's the best way to approach this? Environment: PHP 5.2, Zend Framework...

Can i write prepared statements in procedural php?

The example from the PHP manual is using OOP. Is there a way to do it procedurally? ...

how store date in mysql database?

i have date in dd/mm/yyyy format. how can i store it in databse, if i fant to do some operations on them after? for example i must find out the rows, where date > something what type i must set to date field? thanks ...

Maintain case sensitivity when importing db into windows

I have an export from a MYSQL database on a linux machine however when importing that database into MYSQL on windows all of the table names that were camel cased are now all lower case. The sql dump has the correct case in it but the importing via phpmyadmin seams to remove these. How can I import it and keep the case? Thanks ...

Decrypting an AES encryted column in mysql and adobe flex/flash builder?

How do i decrypt an aes encryted column when using flash/flex to display the table data? adobe flex connected using php connection class to mysql for php, we would use aes_decrypt() however i am not sure how i can get flex to do this. ...

can't commit or rollback, MySQL out of sync error on .net

Im having trouble with a stored procedure, I can't commit after I execute it. Its showing this error "[System.Data.Odbc.OdbcException] = {"ERROR [HY000] [MySQL][ODBC 5.1 Driver]Commands out of sync; you can't run this command now"}" The SP by itself works fine. does anyone have idea of what might be happening? thanks. UPDATE: in C# I ...

Truncating table does not make the mysql file smaller

I truncated several huge tables, but the mysql file and database appears to remain at the same size. Does truncate table delete the data from mysql as well? ...

MySQL - How to convert data_length to actual size?

I am trying to optimize the database buffer size, but i am finding the SHOW TABLE STATUS rather cryptic. I know that the data length is "165719261184"- how do i convert that into actual megabytes? ...

MySQL - What is wrong with this query or my database? Terrible performance.

SELECT * from `employees` a LEFT JOIN (SELECT phone1 p1, count(*) c, FROM `employees` GROUP BY phone1) b ON a.phone1 = b.p1; I'm not sure if it is this query in particular that has the problem. I have been getting terrible performance in general with this database. The table in question has 120,000 rows. I have tried this particular q...

MySQL and PHP counting the minutes and hours from registration date?

How do I count the minutes and hours since a member registered from there registration date that is stored in my MySQL database as datetime using PHP & MySQL. Here is what I have so far. date('F j, Y g:i:s A', strtotime($row['rdate'])) ...

MySQL Group By and bracketing according to age.

Is that possible that MySQL can handle grouping of data according to age bracket? In my users table, age value is their actual age. I want to group them according to age bracket. For example: ages below 1 year old as age1, 1-4 yrs as age2, 5-9 yrs. old as age3 and so on. ...

Installing databases after installation of server.

Hello, I my software (coded in C# .NET ) I need the client to install a database server (MySQL in my case). I'm deploying the setup and making user install it like a pre-requisite. After installing the DBMS server. I need to setup databases (that I created) on the client machine. How to do this automatically (as part of installation.)...

Beginner's Question about accessing mysql using OOP

I am reading the PHP and mySQL web development book and so far been doing all the PHP and mysql using procedural. But then it talks about accessing mysql with objects. This works for me: //I define $db so can connect $query="select * FROM testing"; $result=mysqli_query($db,$query); while($row=mysqli_fetch_array($result)){ //echo the d...

MYSQL Inserting rows that reference main rows.

I'm transferring my access logs into a database. I've got two tables: urlRequests id : int(10) host : varchar(100) path: varchar(300) unique index (host, path) urlAccesses id : int(10) request : int(10) <-- reference to urlRequests row ip : int(4) query : varchar(300) time : timestamp I need to insert a row into urlAccesses for ever...

Using strtotime for dates before 1970

I have a text column in mysql and it stores a date value in the format yyyy-mm-dd. Now, in my php page, I use this code to parse into a date value. date("F j, Y", strtotime($row['value'])); Now, I just read that strtotime() parses values only after January 1, 1970. I have lot of date values before that date. Is there a work around? I ...

MySQL -- Getting distinct rows in a view.

OK, here goes. I am trying to make a view that shows this: +---------+---------+-------+--------+------------+---------------------+---------------+-------------------+-------------+ | post_id | status | title | body | ip_address | time_stamp | category_name | sub_category_name | post_type | +---------+---------+-------+--...