802 Posted Topics
 | MySQL does not come with regular expression search/replace functions. Therefore you will either have to write a MySQL function which does the following: - find a <p> in the text; - find the matching </p> thereafter; - replace all line breaks in between by spaces. Or you write a piece … |
| | Put all data of the same structure in one table. As long as you have your data properly indexed the table size does not matter much. Make sure that all repetitive fields (like product categories etc.) are either enum fields or in lookup tables. Do *not* use phpMyAdmin for development. … |
| | Use the function last_insert_id() which returns the last inserted auto-increment number - not per table, but per connection. |
| | You will have to modify your framework configuration or code. MySQL does not send any error messages to the web server if not explicitly requested. Look in the framework code for the function calls mysql_errno() or mysql_error() which will most probably lead you to the place which you will have … |
| | Having 7 position tables with the same structure is most likely a bad design flaw. If your query returns a count of 1 with the GROUP BY clause and a higher count without it, that means that all records share the same pos_id and it doesn't make sense to count … |
| | It is not a question of PHP. PHP can display anything which the database delivers. Your database query is a very basic one. I suggest you take a mysql primer course before you ask more questions which you will know how to answer after the first lesson. |
 | |
| | This reeks like really bad table design. You cannot do it dynamically in PHP in one form on one web page because PHP is a server language which does not work in the browser. You have to setup some javascript code in your page which alters the value of the … |
| | What for would you like to limit the number of rows in the database? If you really want, you can check the number of rows in PHP with a construct like [CODE]$count = mysql_num_rows( mysql( 'select * from mytable', $connection )); [/CODE]and disable the insertion of new entries if count … |
| | Yes you can. To be more explicit, show us the code for generating the discount code and the condition under which it should execute. And before you code in PHP use the MySQL command line for testing until you know what you want to code. |
| | Maybe you might use the INSERT IGNORE statement. Generate a unique and reproducible discount code for customers with a certain amount of payments. Create a stored function for it (here called myCodeGenerator). Make the code column a unique key (maybe in conjunction with the customer id). Then use something like … |
 | Presumably it's [CODE]select child.cat_name, child.cat_desc, parent.cat_name from mytable child, mytable parent where child.pk_cat_id = parent.fk_cat_id [/CODE] |
| | Submit a test case with CREATE TABLE and INSERT statements which shows the problem with your query. |
| | You "cannot access your system" -? What is your system? Your web server? Do you mean a browser connection timeout? Or which kind of error? Are those manual mysql server restarts in the log? Or are they induced by the safe_mysqld script? If the latter, the question is, why keeps … |
| | Do not use PHP until you know how to code your MySQL queries. List all tables which you need in the FROM clause and describe their relation in the WHERE condition - which fields have to match in one record? [CODE]select ... (desired fields here) from tbllabchem, tbldoctor_info, tblpatient_info where … |
| | Have a look at the mysql clustering and partitioning facility. You may design your DB as usual and distribute the load to several servers. I don't know how well this works with really huge DBs, but it might be worth considering.  |
| | Re: REPLACE syntax MySQL does not natively support regular expression replacements, therefore there is no built-in functionality for your problem. You could either compile MySQL with regex support (there is an experimental plug-in somewhere on the net), or (more easily) write a function in MySQL which does the replacement. The function would look … |
| | You are asking for problems. In effect you are going to de-normalize your table, which may ease a short-term purpose, but in in the long run you will stumble into problems because auf bad design. Then from your example it is not clear which value for "id" should be saved … |
| | [CODE]SELECT * FROM tblpatient_pass p, tblpatient_info i where p.RelationMR_no = i.MR_no and username='$username'[/CODE] |
| | Consider this: [CODE]drop table if exists mytable; CREATE TABLE mytable ( id int, name char(20), parentid int, position int, rating int ); INSERT INTO mytable VALUES (1, 'mike', 1, 1, 6), (2, 'dave', 1, 2, 5), (3, 'paul', 1, 2, 7), (4, 'john', 1, 2, 3), (5, 'mick', 5, 1, … |
| | Order by the fields sent ASC and datesent DESC and use the first 500 rows. |
| | Probably you do not have access rights for the user "root" from an outside address. Login to 192.168.0.200 and check the permissions for the mysql user root. |
| | You cannot have aggregate and non-aggregate columns from the same table in your query. MySQL does a bad job in not noticing this common error. If you want the single reserve_rec items together with the total of shares, you need two table clauses in your FROM clause. Consider this: [CODE]drop … |
| | A common error is the mixing of aggregating and non-aggregating functions in grouped selects. The select clause "child.*" will select all columns from child, and those which are not part of the "group by" clause will have arbitrary values. MySQL seems to return the values of the first row which … |
| | To get a serious answer, submit a complete test case with all CREATE TABLE statements and some test data. Until then, here a first try: [CODE]SELECT app_questions.quest_id, app_questions.app_question, app_questions.quest_type, app_questions.box_type, app_questions.quest_exception, app_answers.current_yr max(app_answer.quest_answers), FROM app_answers RIGHT JOIN app_questions ON app_answers.quest_id = app_questions.quest_id WHERE app_questions.step_desig='1' and app_answers.chap_id = '357277' and current_yr … |
| | Make sure that the order_id and prod_id which you try to insert are already in the orders and products table. If they are, show your test data to debug this. |
| | I assume that you are working with php. To feed your values into a database, use something like the following model and feed the output to mysql: [CODE]<?php $x = 'a:5:{i:0;a:2:{s:6:"answer";s:2:"CA";s:5:"votes";s:1:"1";}i:1;a:2:{s:6:"answer";s:4:"CIMA";s:5:"votes";s:1:"1";}i:2;a:2:{s:6:"answer";s:4:"ACCA";s:5:"votes";s:1:"0";}i:3;a:2:{s:6:"answer";s:5:"CIPFA";s:5:"votes";s:1:"1";}i:4;a:2:{s:6:"answer";s:24:"MBA(specify in comments)";s:5:"votes";s:1:"1";}}'; $a = unserialize($x); foreach( $a as $id => $entry ) echo "insert into mytable (id, answer, votes) … |
| | Re: Utf 8 mysql How do you set the caret? And how do you measure its position? Probably your display module counts one character per line break while mysql store CR+LF, or vice versa. |
| | Make sure that all parts work in the same character set - database, tables, fields, connection, html encoding, javascript encoding. The position differences most likely are due to one side counting the utf8 2-byte codes as one character, while the other side counts them as two characters. |
| | Replace "sum" with "avg": [CODE]SELECT movies.movienum, movies.moviename, avg(reviews.ratingpoints) as ratings FROM movies INNER JOIN reviews ON movies.movienum = reviews.movienum WHERE reviews.ratingpoints > 0 GROUP BY movienum [/CODE] |
| | Must be a typo. ||, the OR operator, is by no means a number literal. It treats its operands as numbers, so one could argue that 'Student name' figures as a number literal here. |
| | What is the error message? What is the table structure? |
| | job_name in table activity should be job_id. [CODE]select * from employee e, job j, activity a where e.emp_id = a.emp_id and j.job_id = a.job_id and a.date = <selected_date> /* optional: */ and e.name = <selected_name> [/CODE] But you will never learn anything from the homework of others. |
| | |
| | So what is your question? Do you have any mysql code which does not work as expected? Then show it. |
| | It is not clear what you want. In your example, "moni" got lost. How would the result row containing moni look like? |
| | Wether you can connect to the mysql server depends on the combination of username, password and host entry in the mysql.user table. This table is not in your production database, but in a system database named mysql. If username and password match and the server from which the intruder operates … |
| | You have to make sure that all components of the process of inserting your russian language strings use the same encoding. The database, tables and columns should be encoded with the utf-8 character set. Also your connection to the database - be it with the native mysql command line client, … |
| | What are you trying? What is your code? What are the error messages? If you since several months try to master the first steps of PHP/MySQL you should consider to take an introductory course to PHP/MySQL/HTML programming rather than wasting your time poking through the fog. You'll find plenty of … |
| | Show us the result of EXPLAIN <yourquery> for both cases. Make sure that you have a query cache set up. If that does not help, you cannot speed up the query, but you can cache the results of SQL_CALC_FOUND_ROWS in a variable and display it until major changes happen to … |
| | Yes. I wonder, though, why you allow multiple payment_numbers. Make them unique and the primary key. In MySQL: [CODE]create table payment( payment_number integer not null primary key, payment_date date not null, payment_amount float not null, loan_number integer not null, foreign key(loan_number) references loan(loan_number) ); [/CODE] |
| | Re: MySQL Logs Set up a general query log. Cf. [url]http://dev.mysql.com/doc/refman/5.0/en/server-logs.html[/url] Locate your configuration file. It's usually called my.cnf and in /etc/mysql. Locate a log-bin entry in my.cnf or add it, if it's not there. [ICODE]log-bin = <location of logfile>[/ICODE] Restart the mysql server process. |
| | Not in a single query with general validity. You could write a procedure which generates a CREATE TABLE statement with one column for each row of the original table and then execute this statement in a stored procedure. Or you could do it with a script language like PHP. But … |
| | [CODE] drop table if exists plants; create table plants (date char(10), plant integer, value integer); insert into plants values ('date1','1','10'), ('date2','1','12'), ('date3','1','15'), ('date4','2','30'), ('date5','2','34'), ('date3','2','43'); select p1.date, p1.plant, value from plants p1 where p1.date = (select max(date) from plants where plant=p1.plant group by plant ) ; [/CODE] |
| | SELECT DISTINCT * FROM table WHERE date_add(`Date_Column`, interval 14 day) < now() |
| | That depends on the actual format you're using. If you're not keeping track of the date, it's simply [CODE]select * from mytable where timefield <= '08:00' or timefield >= '20:00'[/CODE] |
| | Get rid of the quote marks. [CODE]select 1 in (concat("'","1","'")); -- result: false select 1 in (concat("'1'")); -- result: false select (1 in ('1')); -- result: true [/CODE] And you don't need the group_concat neither: [CODE]select * from table_name where RIGHT_CODE IN (SELECT RIGHT_CODE FROM table_name GROUP BY `GRP_CODE` , … |
 | First try if you can connect to the server using telnet: telnet <HOST_IP> 3306 [replace 3306 with the actual port number of the mysql service; 3306 is the default] If you cannot, you have to log into the server and probably change it's firewall's rules. |
| | Show some test data (in form of INSERT statements) which should give a non-empty result. |
 | Alternatively sort by votes and use the first record: [CODE]select * from portfolio order by votes desc limit 1; [/CODE] |
The End.