mysql

mysql_connect() causes page to not display (WAMP)

I currently have a website running MySQL and PHP in which this is all working. I've tried installing WAMPServer to be able to work on the site on my own computer, but I have been having issues trying to get the site to work correctly. HTML and PHP files work correctly (by going to http://localhost/index.php, etc.). But some of the p...

MySQL: 4 Table "has-many-through" Join?

Let's say I have the following 4 tables (for examples' sake): Owners, Trucks, Boxes, Apples. An owner can have many trucks, a truck can have many boxes and a box can have many apples. Owners have an id. Trucks have an id and owner_id. Boxes have an id and truck_id. Apples have an id and box_id. Let's say I want to get all the apple...

Preserving indentation when inserting HTML from MySQL

I am using MySQL and PHP to populate parts of a site, often with HTML stored in a TEXT field. I like to keep my HTML indented so that the source is neat and easy to read, for example: <body> <div> <p>Blahblah</p> </div> </body> However, when the HTML is pulled from MySQL, I end up with: <body> <div> <p>Blahblahblah</p>...

How to improve INSERT INTO ... SELECT locking behavior

In our production database, we ran the following pseudo-code SQL batch query running every hour: INSERT INTO TemporaryTable (SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true) Now this query itself does not need to be fast, but I noticed it was locking up HighlyContentiousTableInInnoDb, even though i...

dates when saving to mysql database

in my php code I was asking the user to choose day, month and year from some dropdown fields where values where 1, 2, 3 etc instead of 01, 02, 03. these were then combined to form a string like "YYYY-MM-DD" for the insertion in a db (in a date field). Having missed the initial 0, I thought I was sending strings in the wrong format, eg ...

Query Months help

Hey all i am in need of some helpful tips/advice on how to go about my problem. I have a database that houses a "signup" table. The date for this table is formated as such: 2010-04-03 00:00:00 Now suppose i have 10 records in this database: 2010-04-03 00:00:00 2010-01-01 00:00:00 2010-06-22 00:00:00 2010-02-08 00:00:00 2010-02-05 00:...

What will happen if I change the type of a column from int to year?

I have a table in MySQL 4.0 which currently has a year field as a smallint(6) type. What will happen if I convert it directly to a Year type with a query like the following: ALTER TABLE t MODIFY y YEAR(4) NOT NULL DEFAULT CURRENT_TIMESTAMP; When the current members of column y have values like 2010? I assume that because the year ty...

PHP - How to use Php inside Php?

Can someone tell/show me how to use PHP inside PHP. I'm trying to make the URL of an image change depending on what value is in a MySQL database. Here's an example of what I'm trying to do. Bear in mind that $idx already has a value from the URL of the page. <?php $query = "SELECT * FROM comment WHERE uname='$idx'"; $result = mysql_que...

The best database table layout/design for a custom ticket system

I'm developing a custom trouble ticket system for network management and trying to find the best database table layout for following scenario: There are tables representing PON, T1 and T3 lines. Each line may have one or more trouble tickets, but every trouble ticket has some unique fields depend on what type of line this tiket belongs....

Store HTML into MySQL database

I'm trying to store a String which contains HTML in a MySQL database using Longtext data type. But it always says "You have an error in your SQL syntax". I tried to store a normal String and it works. Update: This is the query: st.executeUpdate("insert into website(URL,phishing,source_code,active) values('" + URL + "','" + phishingSt...

MySQL Delete from 1 table, using multiple tables

I would like to delete all the rows found by that query: SELECT cart_abandon.* FROM cart_abandon, cart_product, txn_product, users WHERE cart_abandon.cartid = cart_product.cartid AND cart_product.productid = txn_product.productid AND txn_product.username = users.username AND users.id = cart_abandon.userid AND txn_product.txndate >= cart...

T_BOOLEAN_AND error?

whats wrong with this? anybody help me please.. if(stripos($nerde, $hf) !== false) && (stripos($nerde, $rs) !== false){ @mysql_query("update table set dltur = '3' where id = '".$ppl[id]."'"); } else { //dont do anything } i get T_BOOLEAN_AND error. ...

Does MySQL short-circuit the ORDER BY clause?

Given this SQL: SELECT * FROM mytable ORDER BY mycolumn, RAND() Assuming that mycolumn happens to only contain unique values (and hence, contains enough information to perform the ORDER BY), does MySQL short-circuit the operation and skip evaluating the rest? ...

Unix timestamp and mysql date: birthdate

Hi, I have a really basic question concerning unix timestamp and mysql date. I'm trying to build a small website where users can register and fill in their birthdate. Problem is that unix starts with Jan 01 1970. Now if i calculate age for users, form dates like date('m.d.Y', $unix_from_db) and so on it will fail with users older that 4...

MySQL: Will a column-oriented engine be available in MySQL 5.5?

Will a column-oriented storage engine (e.g. InfiniDB) be available in the core release of MySQL 5.5? Meaning, not as a plugin but available with the core MySQL 5.5 bundle. ...

Why is MySQL with InnoDB doing a table scan when key exists and choosing to examine 70 times more rows?

Hello, I'm troubleshooting a query performance problem. Here's an expected query plan from explain: mysql> explain select * from table1 where tdcol between '2010-04-13 00:00' and '2010-04-14 03:16'; +----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+ | id | select_ty...

Upload Excel or CSV file to MySQL with PHP

I'm looking to allow users to upload an Excel or CSV file to MySQL for a contact management system. Need to be able to allow users to map their columns so that they are imported into the correct column in the table. Anyone know of a good site or tutorial on this? ...

group by, order by, with join

Hey guys, quick question, I have this query, and I am trying to get the latest comment for each topic and then sort those results in descending order (therefore one comment per topic). I have what I think should work, but my join always messes my results up. Somehow, it seems to have sorted the end results properly, but has not taken the...

Creating SVG map from geometry stored in MySQL

I have a group of geometries stored in MySQl (as polygon and as well-known text) representing counties. I can build a table of geometries and color codes after querying some county data (say GDP per capita). What is the best way to export this as an SVG map? I cannot find any reference to SVG conversion in the MySQL documentation. ...

Multi-Column Primary Key in MySQL 5

I'm trying to learn how to use keys and to break the habit of necessarily having SERIAL type IDs for all rows in all my tables. At the same time, I'm also doing many-to-many relationships, and so requiring unique values on either column of the tables that coordinate the relationships would hamper that. How can I define a primary key on...