mysql-query

Fatal error in php (exceeded 60s of exec time)

Hey ppl.. i wrote a code to update the password encryption in my site.. the code is.. <?php $db= mysql_pconnect("localhost","root","root"); mysql_select_db("nitconnect"); $query="select password from register limit 1000"; $result=mysql_query($query); $c=0; while($ans=mysql_fetch_array($result)) { $newpass=sha1($ans[0].'Q*iV%qKz$&!C...

MySQL query for current GMT time

This sounds simple enough but I haven't been able to figure out how to use a simple SELECT statement to return the current time in GMT. I have been trying to use CONVERT_TZ() to convert NOW() to GMT based on the server time zone and the GMT time zone but for some reason it returns NULL when I put in the text time zones. The only way I g...

mysql concate check not null

SELECT Concat(mp.cHospital ,',',mp.cHospital1,',',mp.cHospital2) as Hospital FROM TBL I dont want to return null value, how to check not null or how to make isset cond in query ...

MySQL: SELECT Like and char_length

Hi guys! I'm trying to make a query, something like this: SELECT * FROM table WHERE field_name LIKE "keyword%" AND CHAR_LENGTH("keyword%")<20. I know this one is wrong and it's not working, but what's the right way to get the right results? Thank you! ...

SQL query: get tags associated with post

I have three tables. posts | id | title | +---------+-----------+ | 1 | hello | | 2 | goodbye | +---------+-----------+ posts_tags | tag_id | post_id | +---------+-----------+ | 1 | 1 | | 2 | 1 | | 2 | 2 | +---------+-----------+ tags | id | name | +----...

Using an index for a ranged where

im storing down ranks in one of my app, and i frequently do this: select count(*) from table where score > ? and ? = the current person's score. i intend to memcache this, and i use an index on this table, is there a good way to index it? ...

Counting the number of select statements, connections per user in MySQL

Hi guys, how can I count the number of connection and queries by user per day? I need a little list of users, previously registered in my MySQL Server, with the total of the queries and connections. For instance: users | Queries user01 | 120000 user02 | 340000 user03 | 1540000 user04 | 1244000 thanks ...

PHP and mySQL help

Hello there I have a database that has 2 tables in it, one is the categoryTable and the is the userMenuTable, the categoryTable currently has two columns in it, categoryId and categoryTitle it currently holds 2 rows of data, the categoryId's = 1 and 2 and the categoryTitles = News and Blog, in the the userMenuTable I keep a record of wh...

Problem with MAX() and `group by` in an SQL query

Hi, I'm writing a particularly troublesome query. It boils down to this: I obtain a table of the structure: pid | tid | points after a pretty large query. For ease of explanation: pid = problem id tid = team id points = points awarded to that team for that problem. I want to find the team who has scored max points for a parti...

PHP affected rows = 1 wont work properly.

Hi. My script: <?php ob_start(); header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1 header("Expires: Sat, 26 Jul 1997 05:00:00 GMT"); // Date in the past header('Content-type: text/html; charset=utf-8'); include "tilslut.php"; $userid = $_GET["userid"]; $s = mysql_query("SELECT points, lastpoint FROM member_profile WHERE us...

Need help on a JOIN query that almost works

I have four tables. posts | id | title | +---------+-----------+ | 1 | hello | | 2 | goodbye | +---------+-----------+ posts_tags | tag_id | post_id | +---------+-----------+ | 1 | 1 | | 2 | 1 | | 2 | 2 | +---------+-----------+ comments | id | post_id | c...

search big database

I have a database which holds URL's in a table (along with other many details about the URL). I have another table which stores strings that I'm going to use to perform searches on each and every link. My database will be big, I'm expecting at least 5 million entries in the links table. The application which communicates with the user ...

How to get values for every day in a month.

Data: values date 14 1.1.2010 20 1.1.2010 10 2.1.2010 7 4.1.2010 ... sample query about january 2010 should get 31 rows. One for every day. And values vould be added. Right now I could do this with 31 queries but I would like this to work with one. Is it possible? results: 1. 34 2. 10 3. 0 4. 7 ... ...

Selecting max of a sum of two columns

I have a table comparisons. If I run SELECT comparisonID,stu1Vers,stu2Vers,stu1,stu2 from comparisons WHERE stu1!=stu2 and assignmentid=9; I get something like: +--------------+----------+----------+------+------+ | comparisonID | stu1Vers | stu2Vers | stu1 | stu2 | +--------------+----------+----------+------+------+ | ...

query builder of Visual Studio 2008 with mySQL

Hi, I am trying to filter my results in the query builder of Visual Studio 2008 so that when it executes the query to SELECT data from the field i chose, it will only retrieve last names that start with an user input. I figure this is to be done using the filter tab. SO i put a filter I used different filters but did not work with me. I'...

insert if not exists else just select in mysql

I have one mysql table 'alfa' that will contain the primary key of another table 'beta' in one column. But if the entry in 'beta' can not be found I want to insert the value in 'beta' and use the new key in 'alfa'. Can I do this in one query somehow ? I currently have: INSERT INTO alfa SET c1=(SELECT id FROM beta WHERE name = 'john'); ...

question about mysql query

I have got an mysql table containing data about companies: id, company name, city ,address, telephone. I'm trying to write a querry for getting list of cities where there are more then 10 companies within. Is it possible to achieve that? ...

How to iterate by row through a mysql query in php

Ok, So I am trying to query my database and select all rows that have a certain value. After that I turn the query into an array with mysql_fetch_array(), then I tried iterating by row through the fetched array using a for each loop. <?php $query = mysql_query("SELECT * FROM users WHERE pointsAvailable > 0 ORDER BY pointsAvailable Desc"...

Locate Syntax in mysql

I'm trying to return all rows that content substring in mysql and here is my code SELECT Last_Name, Midle_Name, First_Name, Phone_home FROM contact_info LOCATE (@prefixText, Last_Name) but I'm getting error. please help me with correct syntax for LOCATE in my case. Thank you ...

Make a fake schedule if it doesn't exist in db

I want to pull all the rows from a database for a month and if there are any rows in the beginning or in the end that are missing then I want to create fake rows, so every day there is a schedule that one has to follow. How can I detect missing rows or how can I solve this issue? ...