Search Results

Search found 20838 results on 834 pages for 'mysql num rows'.

Page 160/834 | < Previous Page | 156 157 158 159 160 161 162 163 164 165 166 167  | Next Page >

  • mySQL select and group by values

    - by Foo
    I'd like to count and group rows by specific values. This seems fairly simple, but I can't seem to do it. I have a table set up similar to this: Table: Ratings id pID uID rating 1 1 2 7 2 1 7 7 3 1 5 4 4 1 1 1 id is the primary key, piD and uID are foreign-keys. Rating contains values between 1 and 10, and only between 1 and 10. I want to run some statistics and count the number of ratings with a certain value. In the example above, two have left a rating of 7. So I wrote the following query: SELECT COUNT(*) AS 'count' , 'rating' FROM 'ratings' WHERE pID= '1' GROUP BY `rating` ORDER BY `rating` Which yields the nice result as: count ratings 1 1 1 4 2 7 I'd like to get the mySQL query to include values between 1 and 10 as well. For example: Desired Result count ratings 1 1 0 2 0 3 1 4 0 5 0 6 2 7 0 8 0 9 0 10 Unfortunately, I'm relatively new to SQL and I've been reading through everything I could get my hands on for the past hour, but I can't get it to work. I've been leaning along the lines of a some type of JOIN. If anyone can point me in the right direction, it'd be appreciated. Thanks.

    Read the article

  • Working with PHP and MySQL - need a good and secure design with OO design

    - by Andrew
    I am new to PHP- first time developer. I am working on my web application and it is nearly done; nevertheless, most of my sql was done directly via code using direct mysql requests. This is the way I approached it: In classes_db.php I declared the db settings and created methods that I use to open and close DB connections. I declare those objects on my regular pages: class classes_db { public $dbserver = 'server; public $dbusername = 'user'; public $dbpassword = 'pass'; public $dbname = 'db'; function openDb() { $dbhandle = mysql_connect($this->dbserver, $this->dbusername, $this->dbpassword); if (!$dbhandle) { die('Could not connect: ' . mysql_error()); } $selected = mysql_select_db($this->dbname, $dbhandle) or die("Could not select the database"); return $dbhandle; } function closeDb($con) { mysql_close($con); } } On my regular page, I do this: <?php require 'classes_db.php'; session_start(); //create instance of the DB class $db = new classes_db(); //get dbhandle $dbhandle = $db->openDb(); //process query $result = mysql_query("update user set username = '" . $usernameFromForm . "' where iduser= " . $_SESSION['user']->iduser); //close the connection if (isset($dbhandle)) { $db->closeDb($dbhandle); } ?> My questions is: how to do it right and make it OO and secure? I know that I need incorporate prepared queries- how to do it the best way? Please provide some code

    Read the article

  • Recommended approach for error handling with PHP and MYSQL

    - by iama
    I am trying to capture database (MYSQL) errors in my PHP web application. Currently, I see that there are functions like mysqli_error(), mysqli_errno() for capturing the last occurred error. However, this still requires me to check for error occurrence using repeated if/else statements in my php code. You may check my code below to see what I mean. Is there a better approach to doing this? (or) Should I write my own code to raise exceptions and catch them in one single place? What is the recommended approach? Also, does PDO raise exceptions? Thanks. function db_userexists($name, $pwd, &$dbErr) { $bUserExists = false; $uid = 0; $dbErr = ''; $db = new mysqli(SERVER, USER, PASSWORD, DB); if (!mysqli_connect_errno()) { $query = "select uid from user where uname = ? and pwd = ?"; $stmt = $db->prepare($query); if ($stmt) { if ($stmt->bind_param("ss", $name, $pwd)) { if ($stmt->bind_result($uid)) { if ($stmt->execute()) { if ($stmt->fetch()) { if ($uid) $bUserExists = true; } } } } if (!$bUserExists) $dbErr = $db->error(); $stmt->close(); } if (!$bUserExists) $dbErr = $db->error(); $db->close(); } else { $dbErr = mysqli_connect_error(); } return $bUserExists; }

    Read the article

  • Is it possible to create multi-tiered WHERE statements in mySQL

    - by Brendan
    I'm currently developing a program that will generate reports based upon lead data. My issue is that I'm running 3 queries for something that I would like to only have to run one query for. For instance, I want to gather data for leads generated in the past day submission_date > (NOW() - INTERVAL 1 DAY) and I would like to find out how many total leads there were, and how many sold leads there were in that timeframe. (sold=1 / sold=0). The issue comes with the fact that this query is currently being done with 2 queries, one with WHEREsold= 1 and one with WHEREsold= 0. This is all well and good, but when I want to generate this data for the past day,week,month,year,and all time I will have to run 10 queries to obtain this data. I feel like there HAS to be a more efficient way of doing this. I know I can create a mySQL function for this, but I don't see how this could solve the problem. Thanks!!

    Read the article

  • Quality questionnaire php mysql graphipcs

    - by Marcelo
    Hi, i'm making a questionnaire about a service quality, its contains the options (poor, regular, good, very good). It's contains 6 questions (radio button) and a suggestion box (textbox). In the table of the database i created 6 rows for questions, 1 for suggestion and 1 for date (a friend of mine tole me to use this but i didn't get why). q1) I'm going to atribute a value form 1 to 4 to the radio buttons options, and i'd like to sum every answer for each question, and then divide by the numbers of user that answered that question and give the mean. how am i supposed to to that? I'd also like generate reports of the month, of the year. q2) not only about the questionnaire but for registration too. I need all the fields to be completed, no blank options, if he don't complete all of fields it'll not be submitted and there will be a warning message to the user. q3) about the field type, i'd like it to be the same class that is in the database, i'm having a "problem". Ex: Name(varchar) : 1234(int), in the field 'name' of the table of the database 1234 will be shown as name, and i don't want this, i want only the type that i declared in the construction of the table. q4) i'd also like to know if it's possible to create pizza graphics, about the percentage of each question, is this possible? q5) I'm using phpmyadmin and some of my id's are auto_increment, but 'cause of my tests they at a high number, i'd like to restart to 0 the ids number, is this possible? Thanks for the attention.

    Read the article

  • mysql query not running correctly from inside the application

    - by Mala
    I am completely stumped. Here is my php (CodeIgniter) code: function mod() { $uid = $this->session->userdata('uid'); $pid = $this->input->post('pid'); if ($this->_verify($uid,$pid)) { $name = $this->input->post('name'); $price = $this->input->post('price'); $curr = $this->input->post('curr'); $url = $this->input->post('url'); $query = $this->db->query("UPDATE items SET name=".$this->db->escape($name).", price=".$this->db->escape($price).", currency=".$this->db->escape($curr),", url=".$this->db->escape($url)." WHERE pid=".$this->db->escape($pid)." LIMIT 1"); } header('location: '.$this->session->userdata('current')); } The purpose of this code is to modify the properties (name, price, currency, url) of a row in the 'items' table (priary key is pid). However, for some reason, allowing this function to run once modifies the name, price, currency and url of ALL entries in the table, regardless of their pid and of the LIMIT 1 thing I tacked on the end of the query. It's as if the last line of the query is being completely ignored. As if this wasn't strance enough, I replaced "$query = $this->db->query(" with an "echo" to see the SQL query being run, and it outputs a query much like I would expect: UPDATE items SET name='newname', price='newprice', currency='newcurrency', url='newurl' WHERE pid='10' LIMIT 1 Copy-pasting this into a MySQL window acts exactly as I want: it modifies the row with the selected pid. What is going on here???

    Read the article

  • [MySQL] Load data from .csv applying regex before insert into table

    - by Gabriel L. Oliveira
    I know that there is a code to import .csv data into a mysql table, and I'm using this one: LOAD DATA INFILE "file.csv" INTO TABLE foo FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n"; The data inside this .csv are lines like this example: 08/e0/Breast_Cancer_Res_2001_Nov_2_3(1)_55-60.tar.gz Breast Cancer Res. 2001 Nov 2; 3(1):55-60 PMC13900 b0/ac/Breast_Cancer_Res_2001_Nov_9_3(1)_61-65.tar.gz Breast Cancer Res. 2001 Nov 9; 3(1):61-65 PMC13901 I just want the first part (the .tar.gz path), always on the pattern (letter or number)(letter or number) / (letter or number)(letter or number)/... and the part starting by 'PMC', always on the pattern PMC(number...) where 'number' means a number between 0 to 9 and a letter means a letter between a to z (both upper and lower case) So, applying the LOAD DATA, and the regex, and inserting the result entries on my sql table, the result table should be: 1 08/e0/Breast_Cancer_Res_2001_Nov_2_3(1)_55-60.tar.gz PMC13900 2 b0/ac/Breast_Cancer_Res_2001_Nov_9_3(1)_61-65.tar.gz PMC13901 What should be the SQL command to do all this?

    Read the article

  • jQuery and MySQL

    - by Wayne
    I have taken a jQuery script which would remove divs on a click, but I want to implement deleting records of a MySQL database. In the delete.php: <?php $photo_id = $_POST['id']; $sql = "DELETE FROM photos WHERE id = '" . $photo_id . "'"; $result = mysql_query($sql) or die(mysql_error()); ?> The jQuery script: $(document).ready(function() { $('#load').hide(); }); $(function() { $(".delete").click(function() { $('#load').fadeIn(); var commentContainer = $(this).parent(); var id = $(this).attr("id"); var string = 'id='+ id ; $.ajax({ type: "POST", url: "delete.php", data: string, cache: false, success: function(){ commentContainer.slideUp('slow', function() {$("#photo-" + id).remove();}); $('#load').fadeOut(); } }); return false; }); }); The div goes away when I click on it, but then after I refresh the page, it appears again... How do I get it to delete it from the database? Thanks :) EDIT: Woopsie... forgot to add the db.php to it, so it works now .<

    Read the article

  • MYSQL/PHP: Inserting data via HTML form, same "input name"

    - by Camen
    I'm new to MySQL and as a learning project I'd like to make a recipe database. I'd like to the user to be able to enter ingredients through a simple HTML form but I'm stuck in how to label the form so that I can enter several ingredients into the database at once. I'd like to do something like this: <form method="post" action="insert.php"> Ingredient 1: <input type="text" name="ingredient"><br /> Ingredient 2: <input type="text" name="ingredient"><br /> Ingredient 3: <input type="text" name="ingredient"><br /> <input type="submit" value="Submit"> </form> When I do this, I add rows to the table but they're all empty. I know it's got something to do with me using "ingredient" (the table value where I want to add the ingredient name) several times in the form, but I just don't know how to solve it. I would absolutely love some input on how to make it work.

    Read the article

  • hibernate: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException

    - by user121196
    when saving an object to database using hibernate, sometimes it fails because certain fields of the object exceed the maximum varchar length defined in the database. There force I am using the following approach: 1. attempt to save 2. if getting an DataException, then I truncate the fields in the object to the max length specified in the db definition, then try to save again. However, in the second save after truncation. I'm getting the following exception: hibernate: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails here's the relevant code, what's wrong? public static void saveLenientObject(Object obj){ try { save2(rec); } catch (org.hibernate.exception.DataException e) { // TODO Auto-generated catch block e.printStackTrace(); saveLenientObject(rec, e); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static void saveLenientObject(Object rec, DataException e) { Util.truncateObject(rec); System.out.println("after truncation "); save2(rec); } public static void save2(Object obj) throws Exception{ try{ beginTransaction(); getSession().save(obj); commitTransaction(); }catch(Exception e){ e.printStackTrace(); rollbackTransaction(); //closeSession(); throw e; }finally{ closeSession(); } }

    Read the article

  • When to use a foreign key in MySQL

    - by Mel
    Is there official guidance or a threshold to indicate when it is best practice to use a foreign key in a MySQL database? Suppose you created a table for movies. One way to do it is to integrate the producer and director data into the same table. (movieID, movieName, directorName, producerName). However, suppose most directors and producers have worked on many movies. Would it be best to create two other tables for producers and directors, and use a foreign key in the movie table? When does it become best practice to do this? When many of the directors and producers are appearing several times in the column? Or is it best practice to employ a foreign key approach at the start? While it seems more efficient to use a foreign key, it also raises the complexity of the database. So when does the trade off between complexity and normalization become worth it? I'm not sure if there is a threshold or a certain number of cell repetitions that makes it more sensible to use a foreign key. I'm thinking about a database that will be used by hundreds of users, many concurrently. Many thanks!

    Read the article

  • Determining Best Table Structure for MySQL Performance

    - by Joe Majewski
    I'm working on a browser-based RPG for one of my websites, and right now I'm trying to determine the best way to organize my SQL tables for performance and maintenance. Here's my question: Does the number of columns in an SQL table affect the speed in which it can be queried? I am not a newbie when it comes to PHP or MySQL. I used to develop things with the common goal of getting them to work, but I've recently advanced to the stage where a functional program is not good enough unless it's fast and reliable. Anyways, right now I have a members table that has around 15 columns. It contains information such as the player's username, password, email, logins, page views, etcetera. It doesn't contain any information on the player's progress in the game, however. If I added columns for things such as army size, gold, turns, and whatnot, then it could easily rise to around 40 or 50 total columns. Oh, and my database structure IS normalized. Will a table with 50 columns that gets constantly queried be a bad idea? Should I split it into two tables; one for the user's general information and one for the user's game statistics? I know I could check the query time myself, but I haven't actually created the tables yet and I think I'd be better off with some professional advice on this important decision for my game. Thank you for your time! :)

    Read the article

  • MySql product\tag query optimisation - please help!

    - by Nige
    Hi There I have an sql query i am struggling to optimise. It basically is used to pull back products for a shopping cart. The products each have tags attached using a many to many table product_tag and also i pull back a store name from a separate store table. Im using group_concat to get a list of tags for the display (this is why i have the strange groupby orderby clauses at the bottom) and i need to order by dateadded, showing the latest scheduled product first. Here is the query.... SELECT products.*, stores.name, GROUP_CONCAT(tags.taglabel ORDER BY tags.id ASC SEPARATOR " ") taglist FROM (products) JOIN product_tag ON products.id=product_tag.productid JOIN tags ON tags.id=product_tag.tagid JOIN stores ON products.cid=stores.siteid WHERE dateadded < '2010-05-28 07:55:41' GROUP BY products.id ASC ORDER BY products.dateadded DESC LIMIT 2 Unfortunately even with a small set of data (3 tags and about 12 products) the query is taking 00.0034 seconds to run. Eventually i want to have about 2000 products and 50 tagsin this system (im guessing this will be very slooooow). Here is the ExplainSql... id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra 1|SIMPLE|tags|ALL|PRIMARY|NULL|NULL|NULL|4|Using temporary; Using filesort 1|SIMPLE|product_tag|ref|tagid,productid|tagid|4|cs_final.tags.id|2| 1|SIMPLE|products|eq_ref|PRIMARY,cid|PRIMARY|4|cs_final.product_tag.productid|1|Using where 1|SIMPLE|stores|ALL|siteid|NULL|NULL|NULL|7|Using where; Using join buffer Can anyone help?

    Read the article

  • MySQL + Joomla + remote c# access

    - by Jimmy
    Hello, I work on a Joomla web site, installed on a MySQL database and running on IIS7. It's all working fine. I now need to add functionality that lets (Joomla-)registered users change some configuration data. Though I haven't done this yet, it looks straightforward enough to do with Joomla. The data is private so all external access will be done through HTTPS. I also need an existing c# program, running on another machine, to read that configuration data. Sure enough, this data access needs to be as fast as possible. The data will be small (and filtered by query), but the latency should be kept to a minimum. A short-term, client-side cache (less than a minute, in case a user updates his configuration data) seems like a good idea. I have done practically zero database/asp programming so far, so what's the best way of doing that last step? Should the c# program access the database 'directly' (using what? LINQ?) or setup some sort of Facade (SOAP?) service? If a service should be used, should it be done through Joomla or with ASP on IIS? Thanks

    Read the article

  • Problem with mysql query in paging

    - by jasmine
    I have a very simple paging and mysql query. Im not sure that my query is right: $perPage =4; $page= (isset($GET['page']) && is_numeric($GET['page'])) ? $_GET['page'] : 1; $start = ($page * $perPage ) - $perPage ; if (is_numeric($_GET['cID'])){$cid = $_GET['cID'];} $totalCount = sprintf("SELECT COUNT(*) as 'Total' FROM content WHERE catID = %d", $cid ) ; $count = mysql_query($totalCount); $rowCount = mysql_fetch_array($count); $sql = sprintf("SELECT id, title, abstract, content_image FROM content WHERE catID = %d ORDER BY id DESC LIMIT %d, %d", $cid, $start, $perPage ); $query = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_array($query)){ echo $row['id'].' : '. $row['title'] .'<br>'; } with /categories.php?cID=1&page=2 and /categories.php?cID=1&page=1 The output is: 95 : titlev 94 : titlex 93 : titlec 92 : titleb and not changed. What is wrong in my query? Thanks in advance

    Read the article

  • MySql stored procedure not found in PHP

    - by kaupov
    Hello, I have a trouble with MySql stored procedure that calls itself recursively using PHP (CakePHP). Calling it I receive following error: SQL Error: 1305: FUNCTION dbname.GetAdvertCounts does not exist The procedure itself is following: delimiter // DROP PROCEDURE IF EXISTS GetAdvertCounts// CREATE PROCEDURE GetAdvertCounts(IN category_id INT) BEGIN DECLARE no_more_sub_categories, advert_count INT DEFAULT 0; DECLARE sub_cat_id INT; DECLARE curr_sub_category CURSOR FOR SELECT id FROM categories WHERE parent_id = category_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_sub_categories = 1; SELECT COUNT(*) INTO advert_count FROM adverts WHERE category_id = category_id; OPEN curr_sub_category; FETCH curr_sub_category INTO sub_cat_id; REPEAT SELECT advert_count + GetAdvertCounts(sub_cat_id) INTO advert_count; FETCH curr_sub_category INTO sub_cat_id; UNTIL no_more_sub_categories = 1 END REPEAT; CLOSE curr_sub_category; SELECT advert_count; END // delimiter ; If I remove or comment out the recursive call, the procedure is working. Any idea what I'm missing here? The categories are 2 level deep.

    Read the article

  • Rails 3 MySQL 2 reports an error in what looks to be valid SQL syntax

    - by John Judd
    I am trying to use the following bit of code to help in seeding my database. I need to add data continually over development and do not want to have to completely reseed data every time I add something new to the seeds.rb file. So I added the following function to insert the data if it doesn't already exist. def AddSetting(group, name, value, desc) Admin::Setting.create({group: group, name: name, value: value, description: desc}) unless Admin::Setting.find_by_sql("SELECT * FROM admin_settings WHERE group = '#{group}' AND name = '#{name}';").exists? end AddSetting('google', 'analytics_id', '', 'The ID of your Google Analytics account.') AddSetting('general', 'page_title', '', '') AddSetting('general', 'tag_line', '', '') This function is included in the db/seeds.rb file. Is this the right way to do this? However I am getting the following error when I try to run it through rake. rake aborted! Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group = 'google' AND name = 'analytics_id'' at line 1: SELECT * FROM admin_settings WHERE group = 'google' AND name = 'analytics_id'; Tasks: TOP => db:seed (See full trace by running task with --trace) Process finished with exit code 1 What is confusing me is that I am generating correct SQL as far as I can tell. In fact my code generates the SQL and I pass that to the find_by_sql function for the model, Rails itself can't be changing the SQL, or is it? SELECT * FROM admin_settings WHERE group = 'google' AND name = 'analytics_id'; I've written a lot of SQL over the years and I've looked through similar questions here. Maybe I've missed something, but I cannot see it.

    Read the article

  • MySQL COUNT() total posts within a specific criteria?

    - by newbtophp
    Hey, I've been losing my hair trying to figure out what I'm doing wrong, let me explain abit about my MySQL structure (so you get a better understanding) before I go straight to the question. I have a simple PHP forum and I have a column in both tables (for posts and topics) named 'deleted' if it equals 0 that means its displayed (considered not deleted/exists) or if it equals 1 it hidden (considered deleted/doesn't exist) - bool/lean. Now, the 'specific criteria' I'm on about...I'm wanting to get a total post count within a specific forum using its id (forum_id), ensuring it only counts posts which are not deleted (deleted = 0) and their parent topics are not deleted either (deleted = 0). The column/table names are self explanatory (see my efforts below for them - if needed). I've tried the following (using a 'simple' JOIN): SELECT COUNT(t1.post_id) FROM forum_posts AS t1, forum_topics AS t2 WHERE t1.forum_id = '{$forum_id}' AND t1.deleted = 0 AND t1.topic_id = t2.topic_id AND t2.deleted = 0 LIMIT 1 I've also tried this (using a Subquery): SELECT COUNT(t1.post_id) FROM forum_posts AS t1 WHERE t1.forum_id = '{$forum_id}' AND t1.deleted = 0 AND (SELECT deleted FROM forum_topics WHERE topic_id = t1.topic_id) = 0 LIMIT 1 But both don't comply with the specific criteria. Appreciate all help! :)

    Read the article

  • MySQL: optimization of table (indexing, foreign key) with no primary keys

    - by Haradzieniec
    Each member has 0 or more orders. Each order contains at least 1 item. memberid - varchar, not integer - that's OK (please do not mention that's not very good, I can't change it). So, thera 3 tables: members, orders and order_items. Orders and order_items are below: CREATE TABLE `orders` ( `orderid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `memberid` VARCHAR( 20 ), `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `info` VARCHAR( 3200 ) NULL , PRIMARY KEY (orderid) , FOREIGN KEY (memberid) REFERENCES members(memberid) ) ENGINE = InnoDB; CREATE TABLE `order_items` ( `orderid` INT(11) UNSIGNED NOT NULL, `item_number_in_cart` tinyint(1) NOT NULL , --- 5 items in cart= 5 rows `price` DECIMAL (6,2) NOT NULL, FOREIGN KEY (orderid) REFERENCES orders(orderid) ) ENGINE = InnoDB; So, order_items table looks like: orderid - item_number_in_cart - price: ... 1000456 - 1 - 24.99 1000456 - 2 - 39.99 1000456 - 3 - 4.99 1000456 - 4 - 17.97 1000457 - 1 - 20.00 1000458 - 1 - 99.99 1000459 - 1 - 2.99 1000459 - 2 - 69.99 1000460 - 1 - 4.99 ... As you see, order_items table has no primary keys (and I think there is no sense to create an auto_increment id for this table, because once we want to extract data, we always extract it as WHERE orderid='1000456' order by item_number_in_card asc - the whole block, id woudn't be helpful in queries). Once data is inserted into order_items, it's not UPDATEd, just SELECTed. The questions are: I think it's a good idea to put index on item_number_in_cart. Could anybody please confirm that? Is there anything else I have to do with order_items to increase the performance, or that looks pretty good? I could miss something because I'm a newbie. Thank you in advance.

    Read the article

  • MySQL Join Question

    - by rbaker86
    Hi i'm struggling to write a particular MySQL Join Query. I have a table containing product data, each product can belong to multiple categories. This m:m relationship is satisfied using a link table. For this particular query I wish to retrieve all products belonging to a given category, but with each product record, I also want to return the other categories that product belongs to. Ideally I would like to achieve this using an Inner Join on the categories table, rather than performing an additional query for each product record, which would be quite inefficient. My simplifed schema is designed roughly as follows: products table: product_id, name, title, description, is_active, date_added, publish_date, etc.... categories table: category_id, name, title, description, etc... product_category table: product_id, category_id I have written the following query, which allows me to retrieve all the products belonging to the specified category_id. However, i'm really struggling to work out how to retrieve the other categories a product belongs to. SELECT p.product_id, p.name, p.title, p.description FROM prod_products AS p LEFT JOIN prod_product_category AS pc ON pc.product_id = p.product_id WHERE pc.category_id = $category_id AND UNIX_TIMESTAMP(p.publish_date) < UNIX_TIMESTAMP() AND p.is_active = 1 ORDER BY p.name ASC I'd be happy just retrieving the category id's releated to each returned product row, as I will have all category data stored in an object, and my application code can take care of the rest. Many thanks, Richard

    Read the article

  • Database (MySQL) structuring: pros and cons of multiple tables

    - by Gideon
    I am collecting data and storing it MySQL, for: 75 variables 55 countries Each year I have, at this stage since I am building this tool created a single table, of variables / countries (storing 1 year worth of data). Next year (and for several years after that) a new set of data will be input for each country. There are therefore 3 variables in controlling data returned to a user reviewing all collected data. The general form of any query would be: Show me these specifics variables, for these specific countries, for these specific years. (Show me average age and weight, for USA and Canada, for 2012 and 2009, for example) My question is, it seems that I have two options for arranging this data: -Multiple tables where I create a table of country / variable for each year data is collected - Single table and simply add a column (field) for the year that data relates to. As far as I can tell I could make these database calls with either sructure, but is one more powerful / efficient / quicker, and why? Thanks for your consideration. It's a PDO / PHP interface if that is relevent.

    Read the article

  • Help Optimizing MySQL Table (~ 500,000 records).

    - by Pyrite
    I have a MySQL table that collects player data from various game servers (Urban Terror). The bot that collects the data runs 24/7, and currently the table is up to about 475,000+ records. Because of this, querying this table from PHP has become quite slow. I wonder what I can do on the database side of things to make it as optomized as possible, then I can focus on the application to query the database. The table is as follows: CREATE TABLE IF NOT EXISTS `people` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(40) NOT NULL, `ip` int(4) unsigned NOT NULL, `guid` varchar(32) NOT NULL, `server` int(4) unsigned NOT NULL, `date` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `Person` (`name`,`ip`,`guid`), KEY `server` (`server`), KEY `date` (`date`), KEY `PlayerName` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='People that Play on Servers' AUTO_INCREMENT=475843 ; I'm storying the IPv4 (ip and server) as 4 byte integers, and using the MySQL functions NTOA(), etc to encode and decode, I heard that this way is faster, rather than varchar(15). The guid is a md5sum, 32 char hex. Date is stored as unix timestamp. I have a unique key on name, ip and guid, as to avoid duplicates of the same player. Do I have my keys setup right? Is the way I'm storing data efficient? Here is the code to query this table. You search for a name, ip, or guid, and it grabs the results of the query and cross references other records that match the name, ip, or guid from the results of the first query, and does it for each field. This is kind of hard to explain. But basically, if I search for one player by name, I'll see every other name he has used, every IP he has used and every GUID he has used. <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> Search: <input type="text" name="query" id="query" /><input type="submit" name="btnSubmit" value="Submit" /> </form> <?php if (!empty($_POST['query'])) { ?> <table cellspacing="1" id="1up_people" class="tablesorter" width="300"> <thead> <tr> <th>ID</th> <th>Player Name</th> <th>Player IP</th> <th>Player GUID</th> <th>Server</th> <th>Date</th> </tr> </thead> <tbody> <?php function super_unique($array) { $result = array_map("unserialize", array_unique(array_map("serialize", $array))); foreach ($result as $key => $value) { if ( is_array($value) ) { $result[$key] = super_unique($value); } } return $result; } if (!empty($_POST['query'])) { $query = trim($_POST['query']); $count = 0; $people = array(); $link = mysql_connect('localhost', 'mysqluser', 'yea right!'); if (!$link) { die('Could not connect: ' . mysql_error()); } mysql_select_db("1up"); $sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (name LIKE \"%$query%\" OR INET_NTOA(ip) LIKE \"%$query%\" OR guid LIKE \"%$query%\")"; $result = mysql_query($sql, $link); if (!$result) { die(mysql_error()); } // Now take the initial results and parse each column into its own array while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $name = htmlspecialchars($row[1]); $people[] = array( 'id' => $row[0], 'name' => $name, 'ip' => $row[2], 'guid' => $row[3], 'server' => $row[4], 'date' => $row[5] ); } // now for each name, ip, guid in results, find additonal records $people2 = array(); foreach ($people AS $person) { $ip = $person['ip']; $sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (ip = \"$ip\")"; $result = mysql_query($sql, $link); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $name = htmlspecialchars($row[1]); $people2[] = array( 'id' => $row[0], 'name' => $name, 'ip' => $row[2], 'guid' => $row[3], 'server' => $row[4], 'date' => $row[5] ); } } $people3 = array(); foreach ($people AS $person) { $guid = $person['guid']; $sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (guid = \"$guid\")"; $result = mysql_query($sql, $link); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $name = htmlspecialchars($row[1]); $people3[] = array( 'id' => $row[0], 'name' => $name, 'ip' => $row[2], 'guid' => $row[3], 'server' => $row[4], 'date' => $row[5] ); } } $people4 = array(); foreach ($people AS $person) { $name = $person['name']; $sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (name = \"$name\")"; $result = mysql_query($sql, $link); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $name = htmlspecialchars($row[1]); $people4[] = array( 'id' => $row[0], 'name' => $name, 'ip' => $row[2], 'guid' => $row[3], 'server' => $row[4], 'date' => $row[5] ); } } // Combine people and people2 into just people $people = array_merge($people, $people2); $people = array_merge($people, $people3); $people = array_merge($people, $people4); $people = super_unique($people); foreach ($people AS $person) { $date = ($person['date']) ? date("M d, Y", $person['date']) : 'Before 8/1/10'; echo "<tr>\n"; echo "<td>".$person['id']."</td>"; echo "<td>".$person['name']."</td>"; echo "<td>".$person['ip']."</td>"; echo "<td>".$person['guid']."</td>"; echo "<td>".$person['server']."</td>"; echo "<td>".$date."</td>"; echo "</tr>\n"; $count++; } // Find Total Records //$result = mysql_query("SELECT id FROM 1up_people", $link); //$total = mysql_num_rows($result); mysql_close($link); } ?> </tbody> </table> <p> <?php echo $count." Records Found for \"".$_POST['query']."\" out of $total"; ?> </p> <?php } $time_stop = microtime(true); print("Done (ran for ".round($time_stop-$time_start)." seconds)."); ?> Any help at all is appreciated! Thank you.

    Read the article

  • Need help with transferring data between MySQL db's using PHP

    - by JM4
    In one of the sites I manage, the client has decided to take on ACH/Bank Account administration where it was previously outsourced. As a result, the information submitted in our online form which used to simply store in a single database for processing now must sit in 'limbo' until the funds used for payment have been verified. My original plan is as follows: At the end of an enrollment, all form data is collected and stored in a single MySQL database. Our internal administrator will receive an email notification reminding him enrollments have taken place. He will process the ACH information collected and wait the 3-4 business days needed for payment to clear. Once the payment information has been returned as Good (haven't considered what I will do with the 'bad' yet), the administrator can log into a secure portal which allows him to click a button to 'process' the full information once compared and verified. the process is simplified as: Enrollment complete: data stored in DB 'A' Funds verified and link clicked: data from 'A' is copied to DB 'B' and 'A' is deleted. I have run similar processes with CSV output before and simply used //transfers old data to archive $transfer = mysql_query('INSERT INTO '.$archive.' SELECT * FROM '.$table) or die(mysql_error()); //empties existing table $query = mysql_query('TRUNCATE TABLE '.$table) or die(mysql_error()); but in those cases, ALL data returned was copied and deleted. I only want to copy and delete a single record. Any idea how to accomplish this?

    Read the article

  • please suggest mysql query for this

    - by I Like PHP
    I HAVE TWO TABLES shown below table_joining id join_id(PK) transfer_id(FK) unit_id transfer_date joining_date 1 j_1 t_1 u_1 2010-06-05 2010-03-05 2 j_2 t_2 u_3 2010-05-10 2010-03-10 3 j_3 t_3 u_6 2010-04-10 2010-01-01 4 j_5 NULL u_3 NULL 2010-06-05 5 j_6 NULL u_4 NULL 2010-05-05 table_transfer id transfer_id(PK) pastUnitId futureUnitId effective_transfer_date 1 t_1 u_3 u_1 2010-06-05 2 t_2 u_6 u_1 2010-05-10 3 t_3 u_5 u_3 2010-04-10 now i want to know total employee detalis( using join_id) which are currently working on unit u_3 . means i want only join_id j_1 (has transfered but effective_transfer_date is future date, right now in u_3) j_2 ( tansfered and right now in `u_3` bcoz effective_transfer_date has been passed) j_6 ( right now in `u_3` and never transfered) what i need to take care of below steps( as far as i know ) <1> first need to check from table_joining whether transfer_id is NULL or not <2> if transfer_id= is NULL then see unit_id=u_3 where joining_date <=CURDATE() ( means that person already joined u_3) <3> if transfer_id is NOT NULL then go to table_transfer using transfer_id (foreign key reference) <4> now see the effective_transfer_date regrading that transfer_id whether effective_transfer_date<=CURDATE() <5> if transfer date has been passed(means transfer has been done) then return futureUnitID otherwise return pastUnitID i used two separate query but don't know how to join those query?? for step <1 ans <2 SELECT unit_id FROM table_joining WHERE joining_date<=CURDATE() AND transfer_id IS NULL AND unit_id='u_3' for step<5 SELECT IF(effective_transfer_date <= CURDATE(),futureUnitId,pastUnitId) AS currentUnitID FROM table_transfer // here how do we select only those rows which have currentUnitID='u_3' ?? please guide me the process?? i m just confused with JOINS. i think using LEFT JOIN can return the data i need, or if we use subquery value to main query? but i m not getting how to implement ...please help me. Thanks for helping me alwayz

    Read the article

  • Difficulty Inserting from HTML form into MySQL (created in Workbench)

    - by Chaya Cooper
    I created a MySQL database in Workbench 5.2.35 for purposes of storing and using information submitted in html forms but I'm having difficulty inserting records from the html form. The relevant SQL script was saved as Demo2.sql, the schema is C2F, and the table is customer_info. I wasn't sure if that was the problem, so I tried replacing the database name (Demo2) with the schema name, but that didn't work either. My html file includes: form action="insert.php" method="post" The insert.php file states: ?php $con = mysql_connect("localhost","root","****"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("Demo2", $con); $sql="INSERT INTO customer_info(fname, lname, user, password, reminder, answer) VALUES ('$_POST[fname]','$_POST[lname]','$_POST[user]','$_POST[password]','$_POST[reminder]','$_POST[answer]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "1 record added"; mysql_close($con) ? I've also tried INSERT INTO c2f.customer_info(fname, lname, etc. and INSERT INTO 'c2f'.'customer_info'

    Read the article

< Previous Page | 156 157 158 159 160 161 162 163 164 165 166 167  | Next Page >