Search Results

Search found 34274 results on 1371 pages for 'mysql table'.

Page 144/1371 | < Previous Page | 140 141 142 143 144 145 146 147 148 149 150 151  | Next Page >

  • text indexes vs integer indexes in mysql

    - by imanc
    Hey, I have always tried to have an integer primary key on a table no matter what. But now I am questioning if this is always necessary. Let's say I have a product table and each product has a globally unique SKU number - that would be a string of say 8-16 characters. Why not make this the PK? Typically I would make this field a unique index but then have an auto incrementing int field as the PK, as I assumed it would be faster, easier to maintain, and would allow me to do things like get the last 5 records added with ease. But in terms of optimisation, assuming I'd only ever be matching the full text field and next doing text matching queries (e.g. like %%) can you guys think of any reasons not to use a text based primary key, most likely of type varchar()? Cheers, imanc

    Read the article

  • Generating a text file in MYSQL stored procedure

    - by Pablo
    Hi, I'm new to MySQL, I am trying to create a text file using a stored procedure. I'm currently at the stage where I have a temporary table that contains all of the records that I want to output to a text file. I have the following line at the end of my SP, it works in PHPMYAdmin's query but it does not work when part of a stored procedure the code is as follows: SELECT * into outfile '../../htdocs/VIP/Temp/temp.txt' from tmp_Menu2; note that tmp_Menu2 is a table that only includes one field of type VARCHAR(1000) Any help would be greathly appreciated. Thank you,

    Read the article

  • MySQL index cardinality - performance vs storage efficiency

    - by Sean
    Say you have a MySQL 5.0 MyISAM table with 100 million rows, with one index (other than primary key) on two integer columns. From my admittedly poor understanding of B-tree structure, I believe that a lower cardinality means the storage efficiency of the index is better, because there are less parent nodes. Whereas a higher cardinality means less efficient storage, but faster read performance, because it has to navigate through less branches to get to whatever data it is looking for to narrow down the rows for the query. (Note - by "low" vs "high", I don't mean e.g. 1 million vs 99 million for a 100 million row table. I mean more like 90 million vs 95 million) Is my understanding correct? Related question - How does cardinality affect write performance?

    Read the article

  • MYSQL Fast Insert dependent on flag from a seperate table

    - by Stuart P
    Hi all. For work I'm dealing with a large database (160 million + rows a year, 10 years of data) and have a quandary; A large percentage of the data we upload is null data and I'd like to stop it from being uploaded. The data in question is spatial in nature, so I have one table like so: idLocations (Auto-increment int, PK) X (float) Y (foat) Alwaysignore (Bool) Which is used as a reference in a second table like so: idLocations (Int, PK, "FK") idDates (Int, PK, "FK") DATA1 (float) DATA2 (float) ... DATA7 (float) So, Ideally I'd like to find a method where I can do something like: INSERT INTO tblData(idLocations, idDates, DATA1, ..., DATA7) VALUES (...), ..., (...) WHERE VALUES(idLocations) NOT LIKE (SELECT FROM tblLocation WHERE alwaysignore=TRUE ON DUPLICATE KEY UPDATE DATA1=VALUES(DATA1) So, for my large batch of input data (250 values in a block), ignore the inserts where the idLocations matches an idLocations values flagged with alwaysignore. Anyone have any suggestions? Cheers. -Stuart Other details: Running MySQL on a semi-dedicated machine, MyISAM engine for the tables.

    Read the article

  • effective retrieve for a voting system in PHP and MySQL

    - by Adnan
    Hello, I have a system where registered users can vote up/vote down comment for a picture. Something very similar to SO's voting system. I store the votes in a table with values as such; vote_id | vote_comment_id | vote_user_id | vote_date | vote_type Now I have few a question concerned the speed and efficiency for the following; PROB: Once a user opens the picture page with comments I need, if that user has already voted UP/DOWN a comment to show it like; "you voted up" or "you voted down" next to the comment (in SO it the vote image is highlighted) MY POSSIBLE SOL: Right now when I open a picture page for each comment I loop thru, I loop thru my table of votes as well and check if a user has voted and show the status (I compare the vote_user_id with the user's session). How efficient is this? Anyone have a better approach to tackle this kind of problem?

    Read the article

  • MySQL problem: How to get desired rows.

    - by Joonas Köppä
    I have been trying to solve this problem for 2 hours now but I cant understand the solutions others have given people with a similar problem. Ive seen some answers but can't apply it to my own needs. I have a table of users and their times in different sports events. I need to make a scoretable that shows the user with the best time, second best etc. The table before sorting and retrieving looks as follows: | Name | Time | Date | '''''''''''''''''''''''''''''''''''''''''''''' | Jack | 03:07:13 | 2010-12-01 | | Peter | 05:03:12 | 2010-12-03 | | Jack | 03:53:19 | 2010-12-04 | | Simon | 03:22:59 | 2010-12-02 | | Simon | 04:01:11 | 2010-12-09 | | Peter | 03:19:17 | 2010-12-06 | '''''''''''''''''''''''''''''''''''''''''''''' | Name | Time | Date | '''''''''''''''''''''''''''''''''''''''''' | Jack | 03:07:13 | 2010-12-01 | | Peter | 03:19:17 | 2010-12-06 | | Simon | 03:22:59 | 2010-12-02 | '''''''''''''''''''''''''''''''''''''''''' I know answers to this problem lie in another question asked on this very site: CLICK HERE I just have no idea how to apply it to fullfill my needs. Help is highly appreciated. Thank you -Joonas

    Read the article

  • MySQL top count({column}) with a limit

    - by Josh K
    I have a table with an ip address column. I would like to find the top five addresses which are listed. Right now I'm planning it out the following: Select all distinct ip addresses Loop through them all saying count(id) where IP='{ip}' and storing the count List the top five counts. Downsides include what if I have 500 ip addresses. That's 500 queries I have to run to figure out what are the top five. I'd like to build a query like so select ip from table where 1 order by count({distinct ip}) asc limit 5

    Read the article

  • PHP/MySQL User system w/ groups.

    - by Ben C
    I have a user system set up in a 'users' table, and I have groups set up in a 'groups' table. Essentially, I want users to be able to join any and all the groups that they want, in the same was as one would on facebook. How would one go about structuring this in a mysql/php database system? Just a quick summary would be helpful! I've looked around, but I can only find info on creating 'groups' where the users just have a sort of 1, 2 or 3 permission rank. Thanks!

    Read the article

  • horizontally scrollable table column

    - by Alexandru Cucu
    Hello, I would need to build a html table that has a horizontally scrolable column. The scroll should be placed in the column's header. My question first question is: do you know any jquery plug-in that is able to do this? My second question: is this possible using a single table. I've heard that in order to do this you need to use multiple synchronized tables that look like a single one from the user's perspective. Any ideea/advice would be welcomed. Thank you.

    Read the article

  • Retrieve Table Row Index of Current Row

    - by Jon
    Hi Everyone, I am trying to validate a text input when it loses focus. I would like to know which row of the table it is in. This is what I have so far and it keeps coming back as undefined. Any ideas? $("div#step-2 fieldset table tbody tr td input").blur(function() { var tableRow = $(this).parent().parent(); if ($.trim($(this).val()) == "") { $(this).addClass("invalid"); alert(tableRow.rowIndex); $(this).val(""); } else { $(this).removeClass("invalid"); checkTextChanges(); } });

    Read the article

  • MySQL and Collation

    - by user294787
    I have a table with a column using utf8_unicode_ci character set. This table stores Japanese data and my problem is that using this character set, I'm not able to store the same word written in katakana and hiragana because it's considered to be the same word. For example ??? and ???, which mean I, me. I know that I can change the character set to utf8_general_ci to resolve this problem but is it possible to bypass this limitation ? I mean, keep utf8_unicode_ci character set and make those two words be inserted? Is it possible to make this work using CONVERT or CAST operators? Thanks.

    Read the article

  • fetching rows from mysql table and displaying them as JGROWL notifications

    - by jeansymolanza
    hey guys, i am having problems implementing my php/mysql code into a jgrowl if you are familiar with jgrowl you will know it delivers notifications like Growl does for OS X i am trying to get it read all the records from my table but at the moment it is only displaying one record as a notification and it loops through it 4 times another problem is that if i have 5 rows in the table then jgrowl will only display 4 notifications are going to be viewed how do i get it to view all the records in the table as notifications and how do i display the total number of records (5) as notifications and account for the missing one at the moment thanking you guys in advance... God bless <script type="text/javascript"> // In case you don't have firebug... if (!window.console || !console.firebug) { var names = ["log", "debug", "info", "warn", "error", "assert", "dir", "dirxml", "group", "groupEnd", "time", "timeEnd", "count", "trace", "profile", "profileEnd"]; window.console = {}; for (var i = 0; i < names.length; ++i) window.console[names[i]] = function() {}; } (function($){ $(document).ready(function(){ // This specifies how many messages can be pooled out at any given time. // If there are more notifications raised then the pool, the others are // placed into queue and rendered after the other have disapeared. $.jGrowl.defaults.pool = 5; var i = 1; var y = 1; setInterval( function() { if ( i < <?php echo $totalRows_comment; ?> ) { <?php echo '$.jGrowl("'.$row_comment['comment'].'",'; ?> { sticky: true, log: function() { console.log("Creating message " + i + "..."); }, beforeOpen: function() { console.log("Rendering message " + y + "..."); y++; } }); } i++; } , 1000 ); }); })(jQuery); </script> <p> </span> <p>

    Read the article

  • free sql script to get a list of countries, provinces/states and their cities.

    - by reggie
    I am working on a registration page for a php website as mysql as the backend database. I need a sql script to insert the list of countries with their associated provinces and the provinces with their associated cities. I need all the countries, provinces and cities all over the world which are related to each other. I can get the individual list of countries, provinces and cities but there is no list that relates them together.. any help appreciated.. thanks in advance.

    Read the article

  • PDO update with conditional?

    - by dmontain
    I have a PDO mysql that updates 3 fields. $update = $mypdo->prepare("UPDATE tablename SET field1=:field1, field2=:field2, field3=:field3 WHERE key=:key"); But I want field3 to be updated only when $update3 = true; Is this possible to accomplish with a single query? I could do it with 2 queries where I update field1 and field2 then check the boolean and update field3 if needed in a separate query. But hopefully there is a way to accomplish this in 1 query?

    Read the article

  • MySQL - how long to create an index?

    - by user293594
    Can anyone tell me how adding a key scales in MySQL? I have 500,000,000 rows in a database, trans, with columns i (INT UNSIGNED), j (INT UNSIGNED), nu (DOUBLE), A (DOUBLE). I try to index a column, e.g. ALTER TABLE trans ADD KEY idx_A (A); and I wait. For a table of 14,000,000 rows it took about 2 minutes to execute on my MacBook Pro, but for the whole half a billion, it's taking 15hrs and counting. Am I doing something wrong, or am I just being naive about how indexing a database scales with the number of rows?

    Read the article

  • select random value from each type

    - by Joseph Mastey
    I have two tables, rating: +-----------+-----------+-------------+----------+ | rating_id | entity_id | rating_code | position | +-----------+-----------+-------------+----------+ | 1 | 1 | Quality | 0 | | 2 | 1 | Value | 0 | | 3 | 1 | Price | 0 | +-----------+-----------+-------------+----------+ And rating_option +-----------+-----------+------+-------+----------+ | option_id | rating_id | code | value | position | +-----------+-----------+------+-------+----------+ | 1 | 1 | 1 | 1 | 1 | | 2 | 1 | 2 | 2 | 2 | | 3 | 1 | 3 | 3 | 3 | | 4 | 1 | 4 | 4 | 4 | | 5 | 1 | 5 | 5 | 5 | | 6 | 2 | 1 | 1 | 1 | | 7 | 2 | 2 | 2 | 2 | | 8 | 2 | 3 | 3 | 3 | | 9 | 2 | 4 | 4 | 4 | | 10 | 2 | 5 | 5 | 5 | | 11 | 3 | 1 | 1 | 1 | | 12 | 3 | 2 | 2 | 2 | | 13 | 3 | 3 | 3 | 3 | | 14 | 3 | 4 | 4 | 4 | | 15 | 3 | 5 | 5 | 5 | +-----------+-----------+------+-------+----------+ I need a SQL query (not application level, must stay in the database) which will select a set of ratings randomly. A sample result would look like this, but would pick a random value for each rating_id on subsequent calls: +-----------+-----------+------+-------+----------+ | option_id | rating_id | code | value | position | +-----------+-----------+------+-------+----------+ | 1 | 1 | 1 | 1 | 1 | | 8 | 2 | 3 | 3 | 3 | | 15 | 3 | 5 | 5 | 5 | +-----------+-----------+------+-------+----------+ I'm totally stuck on the random part, and grouping by rating_id has been a crap shoot so far. Any MySQL ninjas want to take a stab? Thanks, Joe

    Read the article

  • MySQL: Transactions across multiple threads

    - by Zombies
    Preliminary: I have an application which maintains a thread pool of about 100 threads. Each thread can last about 1-30 seconds before a new task replaces it. When a thread end, that thread almost always will result in inserting 1-3 records into a table, this table is used by all of the threads. Right now, no transactional support exists, but I am trying to add that now. So... Goal I want to implement a transaction for this. The rules for whether or not this transaction commits or rollback reside in the main thread. Basically there is a simple function that will return a boolean. Can I implement a transaction across multiple connections? If not, can multiple threads share the same connection? (Note: there are a LOT of inserts going on here, and that is a requirement).

    Read the article

  • mySql table returning JSON that needs formatting for a iPhone UITableview

    - by Michael Robinson
    I have a php query the returns the following JSON format from a table. [{"memberid":"18", "useridFK":"30", "loginName":"Johnson", "name":"Frank", "age":"23", "place":"School", }, It needs the following format: [{"memberid":"18" { "useridFK":"30", "loginName":"Johnson", "name":"Frank", "age":"23", "place":"School",} }, I can figure out where/how to convert this, Where would I create the formatting following: (1) In the php return? (2) the JSON instructions for deserialization? or (3) Some kinb of Obj-C coding instruction? My end use is a simple Drill Down table using the NSObject, so when I select "memberid" row, I'll get the child/detail list on the next UITableview. My Data.plist will look like the following: Root: Dictionary V Rows: Array V Item 0: Dictionary Title: String 18 V Children Array V Item 0 Dictionary Title String 30 etc. Thanks in advance, this site rocks.

    Read the article

  • Order a MySQL result by Date and Time in PHP

    - by DomingoSL
    Hi, i have this code: $datos = mysql_query("SELECT * FROM `usuarios` LIMIT 0, 30 "); Who is a simple MySQL query in php, but i need the result organized by date and time. There is a field in my table who has this value (automatic inserted when the user sing up in my page). So the table is something like this: Id Name DT 1 Domingo 2010-04-26 23:00:00 2 Cesar 2010-04-25 12:00:00 3 Nataly 2010-04-26 08:00:00 DT is a "datetime" field. How can i get the result order by new to old? Thanks!

    Read the article

  • Mysql eliminate user based on conditions

    - by Dustin
    I asked this last week over the weekend and it got buried in the archives before anyone could answer. So forgive me if you've already seen this. I teach classes and want to be able to select those students who have taken one class, but not another class. I have two tables: lessons_slots which is the table for every class such as: -------------------- -ID name slots- -1 basics 10 - -2 advanced 10 - -3 basics 10 - --------------------- The other table is class_roll, which holds enrollment info, such as: -------------------- -sID classid firstname lastname- -1 1 Jo Schmo -2 1 Person Two ... -13 2 Jo Schmo --------------------- What I want to do, I select everyone who has not had the advanced class (for example). I've tried doing SELECT * FROM lessons_slots LEFT JOIN class_roll ON lessons_slots.ID = class_roll.classid WHERE lessons_slots.name != 'advanced' But that doesn't work...All it does is eliminate that row, without eliminating the user. I want Jo Schmo, for example, to not show up in the results. Any ideas?

    Read the article

  • MySQL Select problems

    - by John Nuñez
    Table #1: qa_returns_items Table #2: qa_returns_residues I have a long time trying to get this Result: item_code - item_quantity 2 - 1 3 - 2 IF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 11 THEN item_quantity = qa_returns_items.item_quantity - qa_returns_residues.item_quantity ELSEIF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 12 THEN item_quantity = qa_returns_items.item_quantity + qa_returns_residues.item_quantity ELSE show diferendes END IF I tried this Query: select SubQueryAlias.item_code, total_ecuation, SubQueryAlias.item_unitprice, SubQueryAlias.item_unitprice * total_ecuation as item_subtotal, item_discount, (SubQueryAlias.item_unitprice * total_ecuation) - item_discount as item_total from ( select ri.item_code , case status_code when 11 then ri.item_quantity - rr.item_quantity when 12 then ri.item_quantity + rr.item_quantity end as total_ecuation , rr.item_unitprice , rr.item_quantity , rr.item_discount * rr.item_quantity as item_discount from qa_returns_residues rr left join qa_returns_items ri on ri.item_code = rr.item_code WHERE ri.returnlog_code = 1 ) as SubQueryAlias where total_ecuation > 0 GROUP BY (item_code); The query returns this result: item_code - item_quantity 1 - 2 2 - 2

    Read the article

  • MySQL UNION query from one table + ORDER BY

    - by ilnur777
    I have one table with two queries and I need to sort it with descending type using ORDER BY. Here is my MySQL query that does not work properly: (SELECT `text` FROM `comments` WHERE user_fr='".$user."' && archive='1' ORDER BY `is_new_fr` DESC) UNION (SELECT `text` FROM `message` WHERE user_to='".$user."' && archive='1' ORDER BY `is_new_to` DESC) Description! is_new_fr and is_new_to counts total new messages. Here is my table contant: user_fr | user_to | archive | is_new_fr | is_new_to| text name1 | name2 | 1 | 2 | 0 | testing... name2 | name1 | 1 | 0 | 5 | testing ... I want to make an order that 1st will display note that has more messages to few, or by another words using DESCending type. This is the display on the page I want to do: Open dialog with name2. Messages (5) Open dialog with name1. Messages (2) Thank you!

    Read the article

  • PHP & MySQL deleting multiple rows script problem.

    - by oReiLLy
    I'm trying to delete two tables rows from two different tables at once when a user clicks the delete button, but for some reason I cant get the table rows to delete can some one help me figure out what is wrong with my script? Thanks Here is the MySQL tables. CREATE TABLE cases ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, file VARCHAR(255) NOT NULL, case VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE users_cases ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, cases_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ); Here is the PHP & MySQL script. if(isset($_POST['delete_case'])) { $cases_ids = array(); $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $dbc = mysqli_query($mysqli,"SELECT cases.*, users_cases.* FROM cases INNER JOIN users_cases ON users_cases.cases_id = cases.id WHERE users_cases.user_id='$user_id'"); if (!$dbc) { print mysqli_error($mysqli); } else { while($row = mysqli_fetch_array($dbc)){ $cases_ids[] = $row["cases_id"]; } } foreach($_POST['delete_id'] as $di) { if(in_array($di, $cases_ids)) { $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $dbc = mysqli_query($mysqli,"DELETE FROM users_cases WHERE cases_id = '$delete_id'"); $dbc2 = mysqli_query($mysqli,"DELETE FROM cases WHERE id = '$delete_id'"); } } } Here is the XHTML. <li> <input type="text" name="file[]" size="25" /> <input type="text" name="case[]" size="25" /> <input type="text" name="name[]" size="25" /> <input type="hidden" name="delete_id" value="' . $row['cases_id'] . '" /> </li> <li> <input type="text" name="file[]" size="25" /> <input type="text" name="case[]" size="25" /> <input type="text" name="name[]" size="25" /> <input type="hidden" name="delete_id" value="' . $row['cases_id'] . '" /> </li> <li> <input type="text" name="file[]" size="25" /> <input type="text" name="case[]" size="25" /> <input type="text" name="name[]" size="25" /> <input type="hidden" name="delete_id" value="' . $row['cases_id'] . '" /> </li>

    Read the article

  • Is it possible to call a procedure within an SQL statement?

    - by darren
    Hi everyone I thought I would use a stored routine to clean up some of my more complex SQL statements. From what I've read, it seems impossible to use a stored procedure within an sql statement, and a stored function only returns a single value when what I need is a result set. I am using mySQL v5.0 SELECT p.`id`, gi.`id` FROM `sport`.`players` AS p JOIN `sport`.`gameinstances` AS gi ON p.`id` = gi.`playerid` WHERE (p.`playerid` IN (CALL findPlayers`("Canada", "2002"))) AND (gi.`instanceid` NOT IN (CALL findGameInstances`("Canada", "2002"))); For example, the procedures 'findPlayers' and 'findGameInstances' are are stored routines that execute some SQL and return a result set. I would prefer not to include their code directly within the statement above.

    Read the article

  • MySQL Column Value Pivot

    - by manyxcxi
    I have a MySQL InnoDB table laid out like so: id (int), run_id (int), element_name (varchar), value (text), line_order, column_order `MyDB`.`MyTable` ( `id` bigint(20) NOT NULL, `run_id` int(11) NOT NULL, `element_name` varchar(255) NOT NULL, `value` text, `line_order` int(11) default NULL, `column_order` int(11) default NULL It is used to store data generated by a Java program that used to output this in CSV format, hence the line_order and column_order. Lets say I have 2 entries (according to the table description): 1,1,'ELEMENT 1','A',0,0 2,1,'ELEMENT 2','B',0,1 I want to pivot this data in a view for reporting so that it would look like more like the CSV would, where the output would look this: --------------------- |ELEMENT 1|ELEMENT 2| --------------------- | A | B | --------------------- The data coming in is extremely dynamic; it can be in any order, can be any of over 900 different elements, and the value could be anything. The Run ID ties them all together, and the line and column order basically let me know where the user wants that data to come back in order.

    Read the article

< Previous Page | 140 141 142 143 144 145 146 147 148 149 150 151  | Next Page >