mysql

How to design a database which has single category and n number of sub categories using mysql

Hi How to design a database which has single category and n number of sub categories using mysql Example: category catId categoryName 1 cat 1 2 cat 2 3 cat 3 4 cat 4 subcategory subCatId subCategoryName catId 1 subcat1 1 2 subcat2 1 3 subcat3 ...

Java/MySQL - datetime problem

Hi, !!IMPORTANT!! Solution found, you need to use a sql.timestamp. Although there are 2 problems with the timestamp. First of all if you want to put an Date into an Timestamp you need to do: new Timestamp(date.getTime().getTime()); Kinda weird... Also the months of a Timestamp start at 0, so january is 0. This means 23-02-2010 in Times...

One table, two column MYSQL query question

Consider a MYSQL table called "comments" with 2 fields, "hostname" and "username". How do I return a list sorted by hostname where the 2nd column is a list of usernames associated with each hostname? I can see how to do this with a scripting language like py, but is it possible as a standard sql qurey? If I do "SELECT hostname,count(d...

How to combine 2 MySQL queries?

I was wondering how can I combine these two queries. Here is the MySQL queries. SELECT COUNT(user_id) as users, user_id FROM users_text GROUP BY user_id SELECT SUM(grade_points) as 'points' FROM grades ORDER BY points DESC ...

SWITCH with LIKE inside SELECT query in MySQL-Resolved

I have this Tags table CREATE TABLE IF NOT EXISTS `Tags` ( `id_tag` int(10) unsigned NOT NULL auto_increment, `tag` varchar(255) default NULL, PRIMARY KEY (`id_tag`), UNIQUE KEY `tag` (`tag`), KEY `id_tag` (`id_tag`), KEY `tag_2` (`tag`), KEY `tag_3` (`tag`), KEY `tag_4` (`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=l...

How to select rows based on values in other rows

I have a database full of sports results. I'd like to select some results based on some characteristics of the previous results. Here's the database structure: CREATE TABLE `results` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `date` DATETIME NOT NULL , `home_score` INT NOT NULL , `away_score` INT NOT NULL , `ho...

Group and select last insert in a table

Hi, I've a table to save some file with a simple versionning system like : ID | FILENAME | CONTENT | DATE 1 | index.php | Content of index.php v1 | timestamp 2 | main.css| Some CSS content | timestamp 3 | index.php | Content of index.php v2 | timestamp I need to code a SQL request to find in this table the last inserted content of ea...

What is the best way to calculate distance on earth with 2 coordinates in meters in MYSQL?

I have got 2 sets google maps geo coordinates. Like 51.2, 6.1 and 50.3 and 5.5. How do i calculate the distance between those 2 coordinates in meters with mysql? I'm looking for the geo calculation, with earth radius and stuff. What is the best way to do this? ...

MySQL query not taking advantage of index

I was analizing a query (working on a wordpress plugin named nextgen gallery), this is what I got query: EXPLAIN SELECT title, filename FROM wp_ngg_pictures wnp LEFT JOIN wp_ngg_gallery wng ON wng.gid = wnp.galleryid GROUP BY wnp.galleryid LIMIT 5 result: +----+-------------+-------+-------...

displaying HTML with PHP & MYSQL

displaying html problem with php & mysql Hi basically i have a quick mysql_fetch_array script to display the contents from the db like below: $sql_select_jobs = $db->query("SELECT * FROM `probid_jobs`"); while ($jobs_found = $db->fetch_array($sql_select_jobs)) { $template_output .= "<div class=\"listed-jobs\" style=\"border: 1px solid ...

How I can do client side pagination for DIV elements generated from MYSQL query?

Hi I have this PHP code : $query = "SELECT * FROM news WHERE news_active = 1 AND news_type = 1 ORDER BY id DESC"; $q2 = "SELECT * FROM st_photos WHERE id = 4 LIMIT 1"; $r2 = mysql_query($q2); $row22 = mysql_fetch_array($r2); $news_set = mysql_query($query); $news_set2 = mysql_query($query); if (mysql_num_rows($news...

Mysql: how to select groups having certain values?

Hello. Say there is such table: mysql> SELECT * FROM tags; +---------+--------+ | post_id | tag_id | +---------+--------+ | 1 | 2 | | 1 | 3 | | 1 | 1 | | 2 | 1 | | 2 | 2 | +---------+--------+ 5 rows in set (0.00 sec) Field names are pretty self-explanatory. I want to select post_...

MySQL DISTINCT on a GROUP_CONCAT()

Hi. I am doing SELECT GROUP_CONCAT(categories SEPARATOR ' ') FROM table. Sample data below: categories ---------- test1 test2 test3 test4 test1 test3 test1 test3 However, I am getting test1 test2 test3 test4 test1 test3 back and I would like to get test1 test2 test3 test4 back. Any ideas? Many thanks! ...

Can't hit the index when a variable in SQL?

Following SQL can't hit the index idx_user_userid, and I don't know how to solve it? SET @q = 'abcd'; EXPLAIN SELECT user_id, mobile_num FROM user WHERE user_id = @q; user description: CREATE user( row_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, user_id CHAR(20) NOT NULL, mobile_num CHAR(15) NOT NULL DEFAULT '' ) ENGINE = InnoD...

MySQL case sensitive foreign key for textual data

I have ISO 639-2 language codes (eng, fre, hin, etc.) in english as primary key in my master table. This column is foreign key in may other tables. Now my issue is even though my master have only lower case values, due to human error some values were added in other tables with language id in mixed cases. Even though there was foreign key...

mysql not updating

i have a messaging system and it works fine but i have it so when its read it mysql_querys and sets read to 1. so that way in futer you can tell if its opend. it does not update here is the script for viewing the message where its suppose to update. THANKS <?php session_start(); require "../scripts/connect_to_mysql.php"; if (isset...

Do I need to create Index seperatly for MySQL??

I am facing some problem with MySQL database. actually It was fast when I did testing on My localhost..but when I uploaded to public server it is slow. data s are displaying but taking more time than usual. there are 7 table which opens to read for a single page. so what I did is for first table I added username and password. for last ta...

Fatal error, not quite sure why?

I think this is just something I haven't come across before! $r = $database->currentChallengers($f, $g); while($row=mysql_fetch_assoc($r)) { $u[]=$row['username']; $i[]=$row['userid']; $s[]=$row['streak']; } for ($i = 0; $i < count($u); $i++) { fore...

which is the right Query for MySQL

I know which column should be called in a table ..for example table name is "table1"; column name is "name"; search is "$query"; Now I query MySQL like this mysql_query("SELECT * FROM table1 WHERE name='$query'"); Is this the right way to call?? I feel it took more time. any suggestions?? ...

how can i pass a value of variable defined in python to a procedure in mysql as an input ?

I made a procedure in mysql and i call it using python but it take an input and i need to pass the value of a variable in python to it but the mysql doesn't understand that it is a variable because the variable is not defined in it's list of fields . for example for a code written in python : variable = "someValue" cursor.execute("call...