mysql

Best method for storing data in mysql?

Hi, I have a pretty basic question on which is the preferred way of storing data in my database. I have a table called "users" with each user getting a username and user_id. Now, I want to make a table called "comments" for users to comment on news. Is it better to have a column in comments called "username" and storing the logged in ...

MySQL: @variable vs. variable. Whats the difference? (Part2)

Ok, building off of the last question I asked, How does Mysql handle the where statment in the following code: DELIMITER ;// DROP PROCEDURE IF EXISTS `test`;// CREATE PROCEDURE `test` ( id INT ) BEGIN SELECT * FROM some_table WHERE id = id; END;// What does MySQL do in this case? Does it treat the where clause as some_tabl...

Best practice for dealing with deleted table rows?

Here is my situation. I have the following tables: Product Product Attribute Order Product (references a product_id and an order_id) Order Product Attribute (references an order_product and a product_attribute) When an admin goes in to edit a product attribute (for instance "color"), he may delete that attribute by mistake and later ...

UTC Problems, not sure how to fix them (PHP/MySQL)

I am seeing UTC date and times in all of my date/time fields on my server, I followed it all back to the timezone setup on my webserver being in UTC time... my question is how can I make these into the local users date and time? All the times are stored in UTC on the MySQL server. I have the users City, Region (Prov/State) and Country....

MySQL - Convert Number to English Representation

Is there a particularly easy way to convert a number like "21.08" into "Twenty One and 08/100" using MySQL? I know in Oracle there was a trick you could use that involved working with Julian dates. It would get the job done in a line or so but it doesn't appear to work in MySQL (since it doesn't support Julian dates). It's not a parti...

mysql query

I have a table like this: col1 col2 2001 3001 2002 3001 2003 3001 2004 3002 2002 3003 2001 3003 i want to create it a query that finds out all the combination of items in col1 with a certain item in col1 if they occur to the same item in col2 and the frequency of occurrence. For example, if we want to explore item numbe...

If statement not working

Hello, I want the output of the code below to be "$entry does not exist" if there is no $entry value anywhere under the "site" column in the database. However, this is not happening when I enter in a value for $entry that I know is not under the "site" column in the database. Is there something wrong with my if statement? Thanks in a...

Spatial Index Query in php

I'm trying to write a query in PHP for a spatial index. But I'm having trouble writing the query for the following line of code... SET @p = CONCAT('Polygon((',lat1,'',lon1,',',lat1,'',lon2,',',lat2,'',lon2,',',lat2,'',lon1,',',lat1,'',lon1,'))'); ...

Using the entity framework with a MySQL DB and the model designer doesn't pickup stored proc parameters

I've got the latest Mysql connector that allows you to use the Visual Studio Entity Framework designer. It's been working great, but I just added a stored proc. The Server Explorer loaded it up fine with the parameters specified, but then I added it to the Entity Model & the code it generates doesn't have any input parameters. Here'...

mysql backup preserving relationships

Hi, I would like to know how to backup my data from 2 separate tables (CATEGORIES and SUBCATEGORIES, where SUBCATEGORIES belong to a CATEGORY) in such a way that I can restore the relationship at a later time. I am not sure if mysqldump --opt db_name would suffice. Example: Categories: | ID | name ----------- | 1 | Audio | 9 | Video...

Check the language of string based on glyphs in PHP

I have a MySQL database with book titles in both English and Arabic and I'm using a PHP class that can automatically transliterate Arabic text into Latin script. I'd like my output HTML to look something like this: <h3>A book</h3> <h3>كتاب <em>(kitaab)</em></h3> <h3>Another book</h3> Is there a way for PHP to determine the language o...

MySQL Error 2013

I am facing connection failure to MySQL problem when I run my program for more than couple of days.MySQL Error Code is 2013 while connecting to Database. MySQL server and client programs are both on same machine. I am using FC5 as my OS and MySQL version is 5.0.18. Can anybody throw some light on this? I am getting mysql error 2013 whil...

How can I get all results that are a certain date away from my value?

Using php i want to make the following mysql query SELECT * FROM TABLE WHERE AUTODATE > (24HRS DIFFERENCE FROM CURRENT DATE) the AUTODATE var CURRENT_TIMESTAMP has values in format Y-m-d H:i:s How is that possible? ...

Update mysql with $_POST data from while loop

Update mysql with $_POST data from while loop I'm trying to get this round my head, but i am failing quite hard :[ I have 3 rows in a database which i am echoing out in a while() loop. A user can change search_terms and then save the fields in mysql, however i don't quite know how to do this, as there are 3 rows, so i can't just do; <...

New records with common data

I have a table like this (MySQL 5.0.x, MyISAM): user{id, login, ip, banned} (Banned: 0 false, 1 true) I would like to find all users not banned (banned=0) if at least 5 other users with the same ip have already been banned (banned=1). Thanks for your help! :) ...

Load options for listbbox from Mysql to listbox, controled by other list - Javascript

Hi guys. I don't know the name for this, if did i would search for it. I wanted something like this, i think its uses javascript but I'm not sure how to do it know. In a web page you have 2 listboxs, its content comes from mysql. There is 2 tables in the database, for each listbox, they are both related. Then when you select a item f...

How do I combine 3 SQL queries into 1?

This is written to count how many people have visited within the last day. I want to also include how many have visited in the last week and year and have it output altogether without doing 3 separate queries. SELECT COUNT(updated_at) AS 'TODAY' FROM parts_development.page_views p WHERE updated_at >= DATE_SUB(NOW(),INTERVAL 1 day) GR...

SQL to check if database is empty (no tables)

Hi guys, I need to check if a database is totally empty (no tables) using an SQL query. How can this be done? Thanks for the help! ...

How to profile MySQL

How do I profile a MySQL database. I want to see all the SQL being run against a database. I know you can do this: type set profiling=1; Run your slow query (eg SELECT * FROM messages WHERE fromaddress='xxx';) type SHOW PROFILES; But this seem to only apply to stuff run on the command line, I want to see the results fr...

MySQL: Updating all rows setting a field to 0, but setting one row's field to 1

Is there an efficient way to update a selection of rows' field to 0, but set One of these rows to 1 based on an ID. Basically, I have multiple objects in a database, and I want to toggle between which one is "inuse", so the query will set one of the rows (by id) to inuse=1 and the others to inuse=0. Thanks :) ...