mysql

cronjob/trigger which can update the database everyday

Hi, I am using MYSQL as my database and PHP as my programming language.I wanted to run a cron job which would run until the current system date matches the "deadline(date)" column in my database table called "PROJECT".Once the dates are same an update query has to run which would change the status(field of project table) from "open" to...

I need helping creating a query to salt and md5 a password then confirm the login on a MySQL Table.

I've created a view of a table on a MySQL database to enable another application to use our existing (centralized) clients table. Our passwords are stored as md5(password + salt) + ":" + salt Normally I decode this via a programming language of the given app we're connecting to it but... This time it's a third party app and I only h...

PDO Catch result

How does one catch/echo the insert results? I have a insertion statement that say that no errors occured but nothing is actually placed in the database. ...

Maximum execution time in phpMyadmin

Hello When I try to execute (some) queries in phpMyadmin I get this error Fatal error: Maximum execution time of 60 seconds exceeded in C:\xampp\phpmyadmin\libraries\dbi\mysql.dbi.lib.php on line 140 because I have a very large table (over 9 millions records) I have edited the file C:\xampp\php\php.ini and changed the valu...

How to select all values, not just those in where clause

I have a query that looks like this: SELECT id, GROUP_CONCAT(type SEPARATOR ',') AS type FROM rock_types WHERE type IN("DWS","Top rope") GROUP BY id this returns all the ids that have a type of DWS or top rope or both with the types concatenated. However, there are more types than just those 2. Is it possible to still select ...

Using ORDER and GROUP with MySQL to get the highest number for each GROUP

I have made a simple auction section on my site, and I would like to display the user's current high bid on their My Bids page. I have a table that holds each unique bid that has the unique auction_id. My current query is as follows, but this only orders the groups instead of ordering what is inside the groups as well. I only want the...

generate a "create table" sql command based on an existing table in mysql

I have a mysql shell but for security reasons I cannot run the mysqldump command. I have a table that I made a while ago with a lot of columns, and I want to generate a new "create table" command to create that table on a different database. Is there some command I can run in the mysql shell to generate that? Thanks. ...

Database Design: Composite key vs one column primary key

A web application I am working on has encountered an unexpected 'bug' - The database of the app has two tables (among many others) called 'States' and 'Cities'. 'States' table fields: ------------------------------------------- idStates | State | Lat | Long ------------------------------------------- 'idStates' is an auto...

Syncing a mobile (iPhone) app with web app

Hello, I'm building an iPhone app / web application, and I'm trying to think through the best way to have two-way sync of the databases. I'll probably go with sqlite on the iPhone and mysql on the web (as that's what I know) but am unsure of how to handle keeping them in sync. Here's a sample schema: index_id(autoincrement) | title | ...

Group by and order with joining tables

Hi, I am having a problem sorting results from joining tables that have to be grouped in Mysql. This is my tables setup. Owners Table owner_id | owner_name 1 | Test owner 1 2 | Test owner 2 3 | Test owner 3 Images upload table image_id | image_name | ownerid | upload_date 1 | image1.jpg | 2 | 04-...

MySQL Certification Resources/Tips

i am planning to take MySQL Certification. what are some resources or tips? i got the study guide already. anything else i shld know of? i saw some sample questions provided in the guide. it seems quite theoretical to me. alot of it probably i can do a google to find that info, without memorising them. and theres some open ended ones...

parsing xml and insert data in mysql table

I am parsing an xml file using php simplexml_load_file() and then inserting the desired data in a mysql ISAM table. The problem is that the code works "most" of the times, with 500 internal server errors here and there. The XML file that I am trying to process is big (around 50 MB), and it yields around 25000 rows in the mysql table when...

mySQL - results per number of seconds.

Hello, Im trying to work out (and failing) how I can get the number of sessions (just using that term generically, dont worry too much) per second. I.e. im looking for retention. I have a table that is updated periodically with the number of seconds that a session is active. I want to get a list of:- MAX(`time) - i.e. the highest numb...

PHP MYSQL Hyperlinks and Form Fields

I want to use a hyperlink to load details into an updatable form page. I have 2 php pages. One returning the id of the last 10 records of a MYSQL query and another returning all field values for a specific record into a form, giving the end user the opportunity to update the field values. Can anyone help me link the two so that when I c...

Finding Related Article with the MySQL LIKE Statement

I'm trying to get a list of related articles. Example $title = $fetch_content[article_name]; // example out put "Hello World, Ask A Question" $rel_title = "Select * from tbl_content WHERE status='t' and article_name like '%$title' order by id desc limit 5"; How to separate the "Hello World, Ask A Question" into single keywords. ...

Mysql date problem in different timezones

Hi, I have been facing this date issue since a long time. Have tried a lot of things but in vain. The problem goes like this: The date entered by the user is '2009-08-12'. It gets stored properly in the db. In the GMT+530 timezone the date gets displayed correctly as well. But when I change the timezone to GMT+1 the date gets displaye...

Displaying results of a MySQL temp table using PHP - caching ?

Hello, I am currently having an issue where MySQL is only displaying 1 of my 3 rows in a dynamic Temporary Table ive created in a PHP page. I can confirm how many rows the TmpTable has via: $numrows = mysqli_num_rows($doResults); // returns 3 But when I do my while($rows=mysqli_fetch_array($doResults)){ } Only 1 of the 3 rows are re...

sql syntax error while cflooping in cfquery INSERT INTO

CF8 and MySQL5, for loop within INSERT INTO. I am getting form data and trying to optimize an insert statement to loop within the cfquery. The best case scenario would be that the loop is just around the VALUES in order to have a single INSERT, but I had issues in trying to ID the second iteration and put a comma at the beginning of th...

MySQL JOIN Query taking far too long to complete

I have a table called users with roughly 250,000 records in it. I have another table called staging with around 75,000 records in it. Staging only has one column, msisdn. I want to check to see how many rows in staging are not present in users. I have the following query, which I have tested on a small data subset, and it seems to work ...

locking a table for read still allows for select (mysql)

I need to concatenate a file id before insert, thus want to lock a reference table for read and write. How can I do that? Just locking the table for read, still allows select from that table $SqlLock2 = "LOCK TABLES TableName t1 READ"; mysql_query($SqlLock2) or die(mysql_error()); // Select something from TableName $SqlUnlock = "U...