Date: 10/09/09 (MySQL Communtiy)    Keywords: mysql, database, sql

Anyone know how to store a MySQL database schema (and maybe data from some tables hopefully based on table structure) into SVN?

Source: https://mysql.livejournal.com/137943.html

Date: 09/22/09 (MySQL Communtiy)    Keywords: mysql, database, sql

I found an instruction set that said to list the first, and last name of all employees that had neither 'SON' nor 'DAUGHTER' listed in their dependency files. I came up with a query that gave me the results I wanted by assuming the database only takes SPOUSE, SON, and DAUGHTER, but assuming has always caused me trouble, so I would like to know: Is there any better way I could have gone about approaching this so it's not going by a general assumption of dep_relation = 1, and so forth?

mysql> SELECT * FROM dependent;
+-------------+------------+------------+-------------------+------------------+
| dep_emp_ssn | dep_name   | dep_gender | dep_date_of_birth | dep_relationship |
+-------------+------------+------------+-------------------+------------------+
| 999444444   | Jo Ellen   | F          | 1996-04-05        | DAUGHTER         | 
| 999444444   | Andrew     | M          | 1998-10-25        | SON              | 
| 999444444   | Susan      | F          | 1975-05-03        | SPOUSE           | 
| 999555555   | Allen      | M          | 1968-02-29        | SPOUSE           | 
| 999111111   | Jeffery    | M          | 1978-01-01        | SON              | 
| 999111111   | Deanna     | F          | 1978-12-31        | DAUGHTER         | 
| 999111111   | Mary Ellen | F          | 1957-05-05        | SPOUSE           | 
+-------------+------------+------------+-------------------+------------------+


mysql> SELECT * FROM employee;
+-----------+---------------+----------------+
| emp_ssn   | emp_last_name | emp_first_name |
+-----------+---------------+----------------+
| 999666666 | Bordoloi      | Bijoy          | 
| 999555555 | Joyner        | Suzanne        | 
| 999444444 | Zhu           | Waiman         | 
| 999887777 | Markis        | Marcia         | 
| 999222222 | Amin          | Hyder          | 
| 999111111 | Bock          | Douglas        | 
| 999333333 | Joshi         | Dinesh         | 
| 999888888 | Prescott      | Sherri         | 
+-----------+---------------+----------------+



What I generated was:

SELECT emp_first_name, emp_last_name 
FROM 
	(
	SELECT emp_first_name, emp_last_name,
	dep_relationship, COUNT(dep_relationship) AS count 
	FROM dependent 
	JOIN employee ON dep_emp_ssn = emp_ssn 
	GROUP BY emp_ssn
	) tempTable 
WHERE tempTable.count = 1 
AND tempTable.dep_relationship != 'SON' 
AND tempTable.dep_relationship != 'DAUGHTER';

To get:

+----------------+---------------+
| emp_first_name | emp_last_name |
+----------------+---------------+
| Suzanne        | Joyner        | 
+----------------+---------------+

Source: https://mysql.livejournal.com/137636.html

Date: 08/07/09 (MySQL Communtiy)    Keywords: mysql, database, sql

I am not entirely sure if this is the right place to ask but I thought to give it a shot anyway.
I want to create a new MySQL Database so I downloaded MySQL 4.1.22 for Windows and installed it. After installing it I have the program MySQL Command Line Client and MySQL Server Instance Config Wizard. For both of them I need a root user name and a root password but how do I know those, where can I find them?
i hope you can help me.

Source: https://mysql.livejournal.com/137190.html

Date: 07/20/09 (MySQL Communtiy)    Keywords: php, mysql, xml, database, sql

hey again
i'm trying to convert an oracle db to mysql. so far everything is going ok except for the files in the db. the old admin was storing jpg, pdf, doc, xml files and whatever else you can think of into a blob field in the table on oracle.

is this the best way to do things? i mean sure all you need to do is back up the database and presto you have all the files. but i'm having a hell of a time getting a backup. 440 entries in the table comes up to a 180 megabyte text file. phpmyadmin won't process the file because it times out, sqlyog and navicat are having trouble with the file size saying they're running out of memory.


the closest i've come to completing this transfer is using toad for oracle and outputting the table to an mdb file. then in navicat i use the wizard and import the mdb, it sees the fields of the table perfectly, but the blobs come out having 0k. aside from that small issue i keep getting error 2006 when importing. and lose between 3 and 219 entries depending on the time i'm importing.

so, at a loss, am i doing things right? is it possible to convert oracle blobs to mysql blobs? personally if i had written the original site i would just save a link to the actual file in the db and not store the actual file in the db. does that make sense?


*UPDATE*
so i tried using the migration tool, no luck. i'm missing libraries and my tech guy wasn't here yesterday. the site for the oracle libraries didn't want to work either.
so this is what i ended up doing:
in toad i saved a csv/txt file of the table without the blobs in the csv. cut the size down to 88k.
i uploaded that via sqlyog into the db. no problems.
then in toad i saved the blobs themselves as individual .dat files. so at least i have the files this way.
the db actually has the filename in it, so it was just a simple extracting the filename, copying the .dat files from one folder to another on the server with a simple php script and changing the names to reflect their actual names. all the files can be opened no problem once the name have been changed.

now i just need to link to said files from db to directory on the server.

thanks again for the input :)

Source: https://mysql.livejournal.com/136836.html

Date: 07/14/09 (MySQL Communtiy)    Keywords: mysql, sql

I have some difficulties in building a right query for a situation. A MySQL Table 'patients' looks like this

Name Doctor Date
-------------------------------
John Deere Dr.Watson 2009-02-11
Bill Fairy Dr.Dylan 2009-03-03
John Deere Dr.Dylan 2009-05-19

I'm trying to count records with same Name field AND all other fields in reply must contain appropriate field in LAST record (based in Date field). I'm doing following query:

SELECT *, COUNT(*) AS num FROM patients GROUP BY name, HAVING COUNT(*)>1 ORDER BY date DESC;

I think that ORDER BY DESC helps me to get in reply fields of last record. But in reply I always receive data contained in my oldest record :( I stumbled :) Anyone can help?

Thanks in advance.

Source: https://mysql.livejournal.com/136661.html

Date: 06/23/09 (MySQL Communtiy)    Keywords: mysql, sql

In the EXPLAIN below, is ref: NULL a bad thing?

mysql> explain SELECT * FROM mytable WHERE item_id='09N1320347' AND value_id IN (2,3,4,5,6,7,8,9);
+----+-------------+------------------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | mytable | range | PRIMARY,item_id | item_id | 18 | NULL | 6 | Using where |
+----+-------------+------------------------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

Indexes:
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 2240917 item_id, color_id
item_id INDEX 93371 item_id, value_id

Source: https://mysql.livejournal.com/135843.html

Date: 06/19/09 (MySQL Communtiy)    Keywords: mysql, database, sql

I've been handed a legacy app with a mysql database to extend/upgrade. The system is a mess. Lab tests are stored in a single table, with over 200 columns containing marker values. The labtest table includes the DateTestCollected. A separate table holds patient demographics, including DateOfBirth and Sex.

In the past, we scanned lab results and identified possible abnormalities based on one or more markers. The same cutoff values were used regardless of age or sex. So -- SELECT COUNT(*) FROM labtest WHERE marker_1>$m_1_cutoff_high OR marker_1<$m_1_cutoff_low; Even with the less than ideal schema, indexes on the marker columns made this kind of query work relatively well.

New algorithms are being put into place. Instead of just looking at one or two markers, we now must look at the age & sex to determine which cutoff values to use, then apply our algorithm. I now have 12 possible variations of the same algorithm.

(ie:

WHERE
date_of_test>'2008-01-01' AND
date_of_test<'2008-04-01' AND
(

( age_rage='0-9' AND sex='m' AND ( marker_1 < 876 AND marker_1 > 345) ) OR
( age_rage='10-19' AND sex='m' AND ( marker_1 < 824 AND marker_1 > 312) ) OR
( age_rage='20-29' AND sex='m' AND ( marker_1 < 798 AND marker_1 > 311) )

) OR (

( age_rage='0-9' AND sex='f' AND ( marker_1 < 987 AND marker_1 > 465) ) OR
( age_rage='10-19' AND sex='f' AND ( marker_1 < 813 AND marker_1 > 404) ) OR
( age_rage='20-29' AND sex='f' AND ( marker_1 < 701 AND marker_1 > 209) )

) OR (
/* ect */
)

There are actually 12 variations to check against. This query is very slow, especially when iterating through 200+ markers (200 x 12 variations). EXPLAIN showed that MySQL is scanning all rows (50,000) - as soon as I applied a multi-column index (date_of_test, marker_1) MySQL stopped scanning the entire table.

Under the current schema, each marker, age, sex, and date_of_test column is indexed separately (no multi-column indexes). There are limits to the number of indexes per table.

So that's where I am. I have a legacy system in need of work, but I'm not sure of the best, most efficient way of approaching the problem.

I thought of converting the labtest table to something like (labtest_id, marker_id, marker_value), but that alone doesn't solve the issue of having to find the age & sex of each patient to determine the correct marker cutoff value.

What would be the least painful way of working through this?

Source: https://mysql.livejournal.com/135617.html

Date: 06/05/09 (MySQL Communtiy)    Keywords: mysql, database, sql

Hi Folks. I'm hoping this is a simple question, but its baffling me.

I have a mySQL 5.0.27 DB. I am trying to give people "show view" and "create view" privs for a specific database. When I execute the commands to do this, they seem to be accepted with no problem - no syntax error, no warning. However when I then do a show grants on the person the "view privs" do not show up, and they confirm that they do not have those privs.

I have flushed privileges as well, which was my first thought, but that is not the case.

I do seem to be able to grant or revoke other privs.

Views are new to us, but they were included in 5.0.1 so I should have them.

Anyone have any thoughts?

Source: https://mysql.livejournal.com/135180.html

Date: 03/30/09 (MySQL Communtiy)    Keywords: mysql, database, sql

We are all familiar with how valuable indexes are to mySQL (and in general) database reads. However - and specifically with respect to mySQL - what effect to indexes have to database writes?

A colleague of mine, as we are changing our DB indexing structure, has argued that indexes harm database writes since the data must be written to the database and to the index. This is a compelling argument. However, in my last job in working with Sybase SQL databases we had instances where indexes would greatly increase write efficiency apparently. I had specific experience that this was the case, but no one was able to explain to me how that was the case given what I could see where the data would have to be written in two separate places.

So, how do indexes effect the efficiency of database writes in mySQL?

Source: https://mysql.livejournal.com/133925.html

Date: 02/03/09 (MySQL Communtiy)    Keywords: mysql, database, sql

Folks may remember last week when I was asking questions about mysqldumps. I was doing that in order to get a DB backup for a specific database we wanted to replicate on another server, and I've been working on that.

After some hit and miss I think I nearly got it working, at least to a point where the replication started between the Master and the slave. It seemed to work for a few seconds, then bombed when the slave tried to execute a statement that was for a DB that it didn't have in it.

The point is that of all the several databases on the Master machine, at this point we only want to replicate one on the slave machine. I thought I had had that set up in the slave's my.cnf with the line

replicate-do-db - aid #aid is the database we want to replicate

however High Performance MySQL says that this is not the way to do this since filtering will be done on the current default database. "This is not usually what you want." :-)

The book does indicate that "On the slave, the relicate_* options filter events as the slave SQL thread reads them from the relay log." This make sense to me, and at this point it also makes sense that the log coming from the master has every statement coming into the DB. It goes on to say that "You can replicate or ignore one or more databases (emphasis mine)...based on LIKE pattern matching syntax." There is where I lose it. I understand how to use the LIKE syntax in mySQL statements, but not in this environment.

Is it possible to set up a small battery of statements in the slave my.cnf along the lines of:

replicate_ignore_table = .%

Would that do it?

Is anyone basically doing replication in a simple Master-Slave relationship where you are only replicating one DB? If so, how are you doing it?

THANKS, folks!

Source: https://mysql.livejournal.com/133717.html

Date: 01/28/09 (MySQL Communtiy)    Keywords: mysql, database, sql

Folks, I'm having what I think is a weird problem restoring a DB from a mysqldump. What I'm trying to do is basically copy a DB from one mysql database server to another.

The mysqldump command I used originally was:

mysqldump --quick --add-locks --extended-insert -u root -p dbname > dbname.sql

When this didn't work upon restoration I also tried:

mysqldump --opt -u root -p dbname > dbname.sql

Both these commands created the dbname.sql file with no problems or complaints.

I copied the SQL file over to the target computer. I went into mySQL as root and did:

create database dbname;
use dbname;
source dbname.sql;

The import would start, but it wouldn't get very far. In the end, after not many seconds it would just...stop. Here is an example:

Query OK, 8708 rows affected (0.11 sec)
Records: 8708 Duplicates: 0 Warnings: 0

Query OK, 8750 rows affected (0.12 sec)
Records: 8750 Duplicates: 0 Warnings: 0

Query OK, 8740 rows affected (0.11 sec)
Records: 8740 Duplicates: 0 Warnings: 0

Query OK, 8758 rows affected (0.11 sec)
Records: 8758 Duplicates: 0 Warnings: 0

Query OK, 8745 rows affected (0.12 sec)
Records: 8745 Duplicates: 0 Warnings: 0

^CQuery aborted by Ctrl+C
^CAborted
mysql: 0 files and 1 streams is left open

$

Source: https://mysql.livejournal.com/133572.html

Date: 12/17/08 (MySQL Communtiy)    Keywords: mysql, database, sql

I need to "synchronize" the output of a stored procedure in one database (I don't have access to any tables there, just stored procedures) and a table in another database. So I need to call a remote stored procedure once in a while and populate a local table with its output. For the remote database, I know hostname, port, username, password and db name.

Is this possible in mysql? Any help would be appreciated

Source: https://mysql.livejournal.com/133145.html

Date: 12/11/08 (MySQL Communtiy)    Keywords: mysql, database, sql

I'm starting a new consulting gig that requires me to work on a MySQL database. Typically, I use Rapid SQL as my IDE, but I have been informed that it doesn't support 5.x. What IDEs out there support MySQL 5?

Source: https://mysql.livejournal.com/132964.html

Date: 01/30/17 (HTML Help)    Keywords: mysql, sql

Things have been progressing recently. Been able to push my spirit to move forward and troubleshoot some errors involving form input of data to mysqli and now it's a bit progressing. I'm really looking forward to finish my project. Hopefully next month i would be able to finish it all and maybe publish it for beta. I think I need a definite "MUSTS" lists of task so i would know and scratch it off if it's done and do what needs to be done next. Will do that later, I think.
Meanwhile, I also saw a posts of for rent in quezon city area.. One I would describe is if I am going to base the photo, the place is cool to hangout with, and not just an ordinary place to stay or rent to. There's a patio, a hangout area and I think it would give you a HOME type of vibes or vibration-..ish?..... There's a street, as it says, or foodtrip street area nearby- 'the strEAT' why not name it like that? lol  and nearby University of the Philippines Diliman.

Source: https://htmlhelp.livejournal.com/2507088.html

Date: 04/19/09 (PHP Development)    Keywords: php, mysql, database, sql

[solved, thanks to synergetic]

HiGuys,

Needing a bit of help with MySQL queries. Hopefully someone has the answer, or a pointer at least :)

Say I have a page displaying an item, www.domain.com/item.php?id=12 and at the bottom of that page i want to have a Previous link and a Next link which will link to the previous and next item in the database, how do i call the item numbers? I don't want to just do $id - 1 or +1 becausethe next item might be number 14 for example, not 13.

So before the links are displayed, i need some way of saying the id is 12, look at what the next number is in the id column and select that one. So it's not just a prev/next page link, but prev/next item in the DB link.

Hopefully my laymans terms aren't too confusing!

Any ideas? :)

many thanks,

Paul

Source: https://php-dev.livejournal.com/81861.html

Date: 03/10/09 (PHP Development)    Keywords: php, mysql, database, sql, web

[solved, thanks to thenetimp]

(x-posted to php)

Hey guys,

I'm coding an image gallery to use on my PHP site and need a bit of help with a couple of features I want to code into it. I was hoping someone here could either give me some pointers or send me to a website that will help with either explainations or code snippets :)

1. Firstly, thumbnails. I have created an admin panel where I upload the images and can set various things like the category, title etc, and all that is stored in a MySql database, and the file uploaded to a general directory. But i'd also like to create a thumbnail of the image as it uploads. I've found several scripts that make thumbnails from images already in a directory, but i'd like it to happen as I upload the image. Is that possible? Without having to use some all-in-one image gallery script heh. I'd also need it to resize the image proportionally so that images aren't just squashed into a 100x100 square shape.

2. Secondly, I'd like to "tag" images like with LJ entries, so that on the front end when a picture is being viewed, the list of tags is shown and can be clicked on to show other image thumbnails with the same tag. But i'm not really sure how to go about this... I was just going to have a table field called img_tags and store them all in that per image, but then is that efficient? searching for matches within a db field? any tips or suggestions?

3. oh and as an extra thought, how can i then take those tags and make a tag cloud maybe?

All and any help would be most appreciated! :)

paul
x

Source: https://php-dev.livejournal.com/81475.html

Date: 02/17/07 (PHP Development)    Keywords: php, mysql, browser, css, html, database, sql, web, linux, apache

Schedge, an Austin based company, is reinventing scheduling.

We are looking for an experienced Lead PHP developer.

Have you developed enterprise scale PHP applications?
Have you worked with technologies like AJAX and MVC?

Then this might be the position for you!

You'll be responsible for leading the development efforts of the core application. The position is contract-to-hire with the option of equity in the company.

Key Areas of Responsibility:
* PHP Application and database development
* Work with Designers and other Developers to build complex user interfaces and data interactions
* Write/Update functional specs

Required Experience:
* Enterprise scale PHP experience
* MVC (Model-view-controller) design experience
* Basic DOM Scripting experience
* Basic (X)HTML & CSS experience
* MySQL
* Multi-browser development
* Apache Webserver

Preferred additional experience:
* Experience with the CakePHP (or similar) PHP framework
* Flash or Action Scripting experience
* MySQL clustering
* Code development for Linux servers

You'll be a good fit if:
* You “get the Web”, and understand it’s full potential.
* Work efficiently and get it right, not just "good enough"
* Can set and execute priorities individually and in a group
* Effectively communicate to people of varying levels of technical expertise

Please note: This is a 100% telecommuting position but the ideal (but not required) candidate would be located in Austin, TX and available for weekly meetings.

If you feel you are a good fit for this position please send your resume and rate per hour to jobs@schedge.com

[cross posted] My apologies if you receive this post more then once.

Source: https://php-dev.livejournal.com/75751.html

Date: 01/03/07 (PHP Development)    Keywords: php, mysql, sql

Ищу людей для создания на общественных началах PHP BBMMOG. Понимаю, что это велосипед, который не гоже изобретать. Но важна не только цель, но и процесс!!!

Приветствуются: графика, вебдезайн, идеи, желание создать свой игровой мир и управлять им.
Предполагаемые направления:

Source: https://php-dev.livejournal.com/75394.html

Date: 10/15/04 (Elite PHP Development)    Keywords: php, mysql, database, sql, java

I was lookin around and found this tonight. Very much kewlness, I am hopeing to join in on the fun in the development community so I can add in my ideas to make PHP even better.
This is a little bit of information I picked up on Creating Custom Exceptions and sending data to the parent class from a function in a subclass. Very spiffy.

query("SELECT NOW()");
var_dump($result->fetch_row());
}
catch(ConnectException $exception) {
echo "Connection Error\n";
var_dump($exception->getMessage());
}
catch(QueryException $exception) {
echo "Query Error\n";
var_dump($exception->getMessage());
}
/* Handle exceptions that we weren't expecting */
catch(Exception $exception) {
echo "Who was that masked exception?\n";
var_dump($exception->getMessage());
}

$result->close();
$my->close();

?>

very spiffy, and I will be posting something using the new MySQLi class. Maybe a whole class system for the generation of graphics pulled from a database. Possibly attach that to a javascript paint proggy, or even better a flash paint proggy..... hmmm the posibilities are endless, but i have to get back to my money making... Gotta love it, but it would be so much more fun if i had a project that really challenged my knowledge and abilities, but well get to that someday.
-=Levi=-

Source: https://php-elite.livejournal.com/504.html