mysql

How do I find out if no results are returned from a mysql_query w/out dieing.

I am grabbing the time from a list of ids. Not every id has a time associated with it. How can I tell if there are no results (EG ID is not in the table yet) and then not get lots of Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 9 in /home/aslum/... errors... $tquery = "SELECT time ". ...

SQL Correlated Subquery in INSERT Statement Performance

I'm using the following query to generate a unique new user: INSERT INTO `users` SET `display_name` = CONCAT( 'user', ( IF( EXISTS( SELECT COUNT(`id`) FROM ( SELECT `id` FROM `users` ) AS `A`), (SELECT COUNT(`id`) + 1 FROM ( SELEC...

mysql:connection OK but response error

hi all..this is my code: $Line = mysql_real_escape_string(postVar("showline")); $Model = mysql_real_escape_string(postVar("showmodel")); $NIK = mysql_real_escape_string(postVar("showNIK")); $sql ="SELECT NIK,Line,Model FROM inspection_report"; $sql.="WHERE NIK='".$NIK."' AND Model LIKE '%".$Model."%' AND...

Is it a common practice for PHP/MySQL apps to have Foreign Keys declared?

I am working on an open source PHP/MySQL application I have looked at phpBB, Wordpress, and other apps to see if they specified Foreign Keys or not (to ensure referential integrity) I cannot find that they have Is it a common practice in these types of applications to specify Foreign Keys in the MySQL database structure? ...

MySQL Date, Minutes Add to a value

I cant seem to get this to work, It returns Null SELECT sdt, timeFor, DATE_ADD(TIMESTAMP(sdt), INTERVAL timeFor MINUTE) FROM tbl_day The return keeps returning sdt, timeFor, DATE_ADD(TIMESTAMP(sdt), INTERVAL timeFor MINUTE) '0000-00-00 01:00:00', 15, '' Columns Type sdt DATETIME timeFor BIGINT(20) Any ideas ...

MySQL - Best method to handle this hierarchical data?

This is a followup to: http://stackoverflow.com/questions/3073614/mysql-is-it-possible-to-get-all-sub-items-in-a-hierarchy I have an arbitrary-depth adjacency list model table (I am at the point that I can convert it into a nested set model. I read the MySQL data on how to use a nested set model, though it seemed to get increasingly co...

BLOB or Normalized database with textual data?

I am planning to develop a web-application just to test my knowledge on JSP/Servlets. I am planning to create a very simple "Bug tracker" and I am in the process of designing the database. I am using MySql/JDBC. There are some minor insignificant data like user's DOB, Date of Joining, list of languages/technologies the user is comfortab...

how to set character to big size without press capslock

dear all..i want my input form automatically make all character become big size.. and also without press a capslock button...i want all data which have been input into DB in capital format..what's code to make it? ...

Select only the rows that aren't unique [MySQL]

I'm trying to make a query that will check for duplicates, and if I find duplicates I want to be able to update those accordingly. For example, I have the following data set, how do I only select the 2 rows that have the same value for 'position'? ID position 1 0 2 1 3 2 4 1 5 ...

want to compare a strtotime value to currenttime and output: something x min ago

Hi. I am working on a newsfeed like similar to facebook. Where I compare the time when something happened in my mysql table to the current time and output: something something happened x min ago. First i connect to mysql with this code: $conn = db_connect(); $newsfeed = $conn->query("select info, username, time from ...

How can I make a table in MySQL called "order"?

When I make a MySQL table order, it is created successfully but, when I execute any query against it, it says "error 1064 , syntax error". When I change the name to orders, it works fine. But I don't want to change the name. How can I execute our query against the order table? ...

How to get the result of a query with values in a array?

Hi This is my function where i fetch my results from database function Prof_Viewer($MemberId) { $query = $this->db->query("SELECT distinct(t1.dProfileId) as prof FROM tbl_profile_viewer as t1 JOIN tbl_login as t2 WHERE t1.dProfileViwerId='$MemberId'"); if($query->num_rows > 0) { foreach($query->result() as $row) ...

MySQL MAX(id) called from PHP produces strange value

Hi all, I am just trying to get the auto incremented value of a table that is currently the highest. I do not need to know what the next auto increment is, just the highest value of what is in the table right now. I am using the code below, but regardless of what the actual auto increment is, what table I last inserted into, what table...

How do I loop through a mysql query with php

I tried to use this function $conn = db_connect(); while ($newsfeed = $conn->query("select info, username, time from newsfeed ORDER BY time DESC LIMIT 10")) { (...) echo "<p>User $newsfeed_username just registerted ".$minutes." min ago </p><br>"; but it only shows the la...

Mysql stored procedure giving error at runtime

I have created a stored procedure in Mysql, as : delimiter $$ drop procedure if exists test9$$ Create procedure test9(test_type varchar(20)) Reads sql data begin Declare 1_id int; Declare 1_date varchar(20); Declare done int default 0; Declare cur1 cursor for select id,name from buyers where ticket_type='test_t...

reading from MySQL is faster or reading from a file is faster??

HI I got a doubt I have seen reading mysql data is slower in case of large tables...I have done lots of optimization but cant get through.. what I am thinking is will it give a better speed if I store data in a piece of file?? off course each data will be a separate file. so millions of data = millions of file. I agree it will consum...

Does MySQL support user defined data types

Is it possible to define (alias) a base data type in MySQL? Currently I would like to define UUID as char(32), and then use UUID as the type throughout the schema definition. As we're prototyping at the moment, UUID is very likely to change - I'd like to ensure that this change is reflected consistently throughout the schema. I'm thin...

making a manipulation inside DB...

hii all..i have some code that make all string characters which have been input into DB change to capital size..this is my code: function newinputdata($modelnm,$serialno) $Model=mysql_real_escape_string($modelnm); $Serial_number=mysql_real_escape_string($serialno); $str=strtoupper($str) echo $str; $sql="INSERT INTO........."; is tha...

How many fields is normal to have in one table?

Ok, I am creating a game, I have one table where I save a lot of information about a member, so I have many field in it. How many fields is normal to have in one table? Does it matter? Maybe I should split that info into two-three-four tables? What do you think? ...

Can you tell what is wrong with this query??

HI all I am using single database and near about 7 tables. do have data s filled with all tables. say near about 10k as of now. but will grow further and may strike millions but will take time. my question is why my query is slow fetching results. its taking near about 10 to 12 seconds for a query on non load conditions. I am worried ...