SitePoint Forums - Databases & MySQL

Online school admission system

RizwanAhmedMemon — Thu, 23 May 2013 05:14:48 GMT

Hello programmers!
I have been given a project to make an online school admission system, but I don't have much ideas how to do that. So, I need your help. What things I should consider and how the look of the website should be and how to maintain the database? What to understand first? etc
So, please help me as much as you can. By suggesting some samples that you have etc.
Thanks.

Select AVG sale price of all items carried in 'A' when sales location <> 'A'

Billiam80 — Wed, 22 May 2013 20:14:11 GMT

I am having a hard time getting this to work right. Maybe you have a answer....

I have several stores with a different set of items in each store, and all items are in at least two stores.

--I want to find the average sale price of all items carried in 'store A' when the sales location was another store and the date was in a certain range...so store <> 'store A' AND date BETWEEN '2013-xx-xx' AND '2013-xx-xx'...

I have tried several things but mostly looking similar to this:

--Select avg(sales price) FROM (SELECT * from sales where store <> 'store A' AND date BETWEEN '2013-xx-xx' AND '2013-xx-xx') AS sales WHERE item IN (SELECT item from sales where store = 'store A');

The goal is to compare average sales at store A with average sales of like items in different locations. I want to run for 30 stores to compare each location. Can anybody see a better way of getting my desired results. I feel like I might be taking the long road

fetch the max and than if there is equal take max field

whisher — Wed, 22 May 2013 15:22:10 GMT

Hi,

PHP Code:


CREATE TABLE IF NOT EXISTS ingredient(
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    food_id TINYINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    point TINYINT UNSIGNED NOT NULL,
    score INT UNSIGNED DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY unique_ingredient_title (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

so with this query I got the ingredient with the
max score

PHP Code:


SELECT id,food_id,title,MAX(score)
FROM ubi_ingredient 
GROUP BY food_id

but if there is a ingredient
with the same score I want the one with
the max score and I don' find the way ^^

Can you help me, please ?
Thanks in advance.

Very Simple Subquery Returning Strange Results

brandonBuster — Wed, 22 May 2013 13:48:58 GMT

All I want is a single random value from this MySQL query. Both the outer inner and outer queries reference the same table but I'm assigning them different aliases. Yet, this query is still performing like a correlated subquery, often returning multiple results.

Code:

select img.* from images img where img.image_id = (select floor(max(i.image_id) * rand()) + 1 from images i)

The inner query performs as expected if extracted and ran by itself, returning only a single random value.

Random Sort Order on 2nd Column?

DoubleDee — Tue, 21 May 2013 16:15:00 GMT

The following MySQL query returns a listing of Articles for each Sub-Section of my website...

Code SQL:

SELECT ap.ds_subsection_slug, a.slug, a.heading, a.summary, a.image
FROM article AS a
INNER JOIN article_placement AS ap
ON a.slug = ap.article_slug
WHERE ap.sd_section_slug = 'finance'
AND ap.ds_dimension_slug = 'featured-finance'
ORDER BY ap.ds_subsection_slug, a.slug

My goal is to "randomize" the order in which Articles are displayed in a given Sub-Section. (Hopefully this will encourage Users to read more Articles?!)

Is there some way in MySQL, to return a result-set first sorted alphabetically by Sub-Section, but then sorted *randomly* by Article??

Sincerely,

Debbie

PROBLEM WITH COMPLEX PHP MYSQL UPDATE

kcay — Tue, 21 May 2013 15:08:23 GMT

Hi all, I've been working on a php script that update school classes in a mysql database.

Below is the script i have worked on thus far and the images of how my database table is and how i want it to be after the update. Basically I want it to retain the "sub_class" when updating the "class" from the "dyn_class".

Code PHP:

 
if(isset($_POST['submit'])) {
 
 
$curYear = date('Y');
$end_year = $curYear+1;
$classA= 'JSS1';
$classB= 'JSS2';
$classC= 'JSS3';
$classD= 'SS1';
$classE= 'SS2';
$classF= 'SS3';
 
$check_all = mysql_query("select * from `student` where d_class='$classF' and year='$curYear'");
$get_all = mysql_fetch_array($check_all);
 
$subclass= $get_all['sub_class'];
 
$query1= mysql_query("update student set d_class='Senior-graduant', class='Senior-graduant', year='$end_year' where d_class='$classF' and year='$curYear'");
 
 
if($query1){
 
$check_all1 = mysql_query("select * from `student` order by `sub_class` desc");
$get_all1 = mysql_fetch_array($check_all1);
 
$subclass2= $get_all1['sub_class'];
 
 
$query2= mysql_query("update student set d_class='$classF', class='$classF$subclass2', year='$end_year' where d_class='$classE' and year='$curYear'");
 
 
if($query2){
 
$check_all2 = mysql_query("select * from `student` order by `sub_class` desc");
$get_all2 = mysql_fetch_array($check_all2);
 
$subclass3= $get_all2['sub_class'];
$query3= mysql_query("update student set d_class='$classE', class='$classE$subclass3' , year='$end_year' where d_class='$classD' and year='$curYear'");
 
 
 
if($query3){
 
$check_all3 = mysql_query("select * from `student` order by `sub_class` desc");
 ($get_all3 = mysql_fetch_array($check_all3);
 
$subclass4= $get_all3['sub_class'];
 
$query4= mysql_query("update student set d_class='Junior-graduant', class='Junior-Graduant' , year='$end_year' where d_class='$classC' and year='$curYear'");
}
 
if($query4){
$check_all4 = mysql_query("select * from `student` order by `sub_class` desc");
$get_all4 = mysql_fetch_array($check_all4);
 
$subclass5= $get_all4['sub_class'];
 
$query5= mysql_query("update student set d_class='$classC', class='$classC$subclass5' , year='$end_year' where d_class='$classB' and year='$curYear'");
}
 
 
if($query5){
$check_all5 = mysql_query("select * from `student` order by `sub_class` desc");
$get_all5 = mysql_fetch_array($check_all5);
 
$subclass6= $get_all5['sub_class'];
 
$query6= mysql_query("update student set d_class='$classB', class='$classB$subclass6' , year='$end_year' where d_class='$classA' and year='$curYear'");
 
 
if($query6){ echo"Classes updated successfully!";} else{echo 'Could not update classes!';}
 
}
 
 
}
 
}
 
}
 
}
 
}
}
 
?>

This is how it looks like before the update.

normal.jpg

This is how I want it to look like after the update.

UPDATED.jpg

Thanks as i await your responses.

Attached Images

normal.jpg (51.5 KB)
UPDATED.jpg (53.9 KB)

Calculate difference of values for records on outer ends of timeframe

Hieronymus — Tue, 21 May 2013 12:38:23 GMT

Hy there,

I'm working on a problem I can't seem te solve in MySQL.

I have a table with snapshots of cummulative values. The information I need is the difference of those values within a timeframe.
At the moment I use PHP to calculate thoses difference because I can't seem te get the min and max date from the table AND the values corresponding to those records.

Code:

SELECT fms1.* 

FROM fms1 

WHERE

AND fms1.gps_unit_id = 114 

AND fms1.created >= "2013-03-01 00:00:00" 

ORDER BY fms1.created ASC 

LIMIT 1

) UNION (

SELECT fms1.* 

FROM fms1 

    WHERE fms1.gps_unit_id = 114 

    AND fms1.created >= "2013-03-01 00:00:00" 

    AND fms1.created <= "2013-03-01 23:59:59" 

    ORDER BY fms1.created DESC 

LIMIT 1

)

This way I get two rows returned for which I calculate the difference for each of those values. (say fms1.odometer, fms1.idle_time and so on)

I tried something like this:

Code:

SELECT

    fms1.*,

    max(fms1.created) AS MaxDate,

    min(fms1.created) AS MinDate

FROM fms1

WHERE fms1.created BETWEEN "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" 

AND fms1.gps_unit_id = 114

This does return the correct MinDate and MaxDate for the records on that day (over a 100), but it only returns the complete record for the MinDate. Which in this case is the correct one. You cannot sort the records by id, because causality is not guarantied.

The thing I can't get my head around is how to select complete records based on the outcome of funtions like min() and max(). For instance if you use

Code:

SELECT

    max(fms1.created) AS MaxDate,

    min(fms1.created) AS MinDate

FROM fms1

WHERE fms1.created BETWEEN "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" 

AND fms1.gps_unit_id = 114

I do get the correct dates, but how to get the records belonging to those values? You can't use min() and max() in subqueries...

Many thanks!

upgrading mysql v4.1 to v5

rooftop — Tue, 21 May 2013 11:17:25 GMT

Hi

I'd like to update the MySQL on our web server from v4.1 to v5 so that my version of cpanel is easier to use.

There may be other benefits too I guess.

I have a 14 client's sites hosted on the web server that use mysql databases.

8 of these are wordpress and joomla databases. The remainder are custom cms's I presume.

1/ Do you think its a good idea for me to upgrade?

2/ And if so do you have any tips for how I best proceed with the upgrade?

Its a supported server to the server support team will perform the upgrade but of course I will be answerable to my clients if their websites stop working.

Thanks

Converting Database From LATIN-1 to UTF-8

Panduola — Mon, 20 May 2013 16:01:40 GMT

Hi there,

I have an older database that is still using LATIN-1 as its encoding and I'd like to switch this to UTF-8 for the database, its tables, and their columns.

Would these queries do the trick?

Code mysql:

alter table TABLENAME convert to CHARACTER SET utf8 COLLATE utf8_unicode_ci;
[each table...]
[each table...]
[each table...]
alter database SCHEMA default character set utf8 COLLATE utf8_unicode_ci;

My questions are:

Do I need to worry about any data/character loss upon conversion of these tables? Or will the conversion be clean?
What about columns that are currently storing HTML data? This data has been htmlspecialchar()'ed in PHP.

Furthermore, as I have been looking into encoding issues for this web app, I discovered that Apache's AddDefaultCharset has been set to ISO-8859-1 since the web app's inception. So, any data that has been submitted to this database via web forms has had an encoding of ISO-8859-1. This has now been set to UTF-8.

In summary: web pages accepting ISO-8859-1 encoding into a LATIN-1 database switching to UTF-8 all around.

Just want to make sure I don't lose any data before the flip. :) What do I need to be aware of?

Thanks in advanced.

Why does my while loop loop only 2 times instead of 10?

Nordy — Mon, 20 May 2013 14:58:53 GMT

Hi Guys,

Here's my table data and my stored procedure in mySql
Table checkval

Code:

Stored Procedure

Code:

BEGIN

DECLARE x INT;

DECLARE done INT DEFAULT FALSE;

DECLARE myType INT;

DECLARE cur1 CURSOR FOR select val from checkval;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 

SET x = 10;

 

WHILE x  >= 2 DO

##############

 

OPEN cur1;

 

read_loop: LOOP

IF done THEN

LEAVE read_loop;

END IF;

 

FETCH cur1 INTO myType;

insert into myType values (myType);

 

 

END LOOP read_loop;                 

CLOSE cur1;

SET  x = x-1; 

 

##################

END WHILE;

 

END

Code:

Current Output

5705

5704

5703

5702

5701--Loops only twice

5705

5704

5703

5702

5701

Code:

Desired Output

5705 - Loops 8 times

5704

5703

5702

5701

5705

5704

5703

5702

5701

5705

5704

5703

5702

5701

5705

5704

5703

5702

5701

5705

5704

5703

5702

5701

5705

5704

5703

5702

5701

5705

5704

5703

5702

5701

5705

5704

5703

5702

5701

In all of this, even when I specify a while loop starting at `10` and ending at `2`, why do I get my output only looped twice in the target table. I was hoping to see it repeated 8 times. Can you pls help me on this? I'm backward with stored procedures, so I guess I'm not putting something in the right place or something like that.

Database Content Metadata

skelleex — Sat, 18 May 2013 20:22:13 GMT

Hi All,

I am trying to build a database which will find music that has similar features however to do such I need to enter metadata. I was wondering what is the best way to do such? Currently I have basic data like track name, genre, artist etc. However I want to go deeper than that and define it as something like slow rockers or fast party music or even combining genres. What is the best way to achieve something like that in terms of storing the extra metadata that describes a track and then doing a query to find other tracks that match closely to it?

Any help will be greatly appreciated.

INSERT SELECT statement

mickyginger — Sat, 18 May 2013 19:03:11 GMT

Hi there,

I having a problem with a MySQL INSERT .. SELECT statement:

Code:

INSERT INTO

`products`

(

  `id`, 

  `image`, 

  `title`, 

  `description`, 

  `price`, 

  `VAT`, 

  `related_products`

) 

SELECT 

  'CR25', 

  `id` FROM `images` WHERE `images`.`title` = 'Some Image Title', 

  'Product Title', 

  'Product Description', 

  4.99, 

  0, 

  'CR26'

Throws an 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 ' 'Product Title'

I've also tried

Code:

INSERT INTO

`products`

(

  `id`, 

  `image`, 

  `title`, 

  `description`, 

  `price`, 

  `VAT`, 

  `related_products`

) 

VALUES

( 

  'CR25', 

  (`id` FROM `images` WHERE `images`.`title` = 'Some Image Title'), 

  'Product Title', 

  'Product Description', 

  4.99, 

  0, 

  'CR26'

)

But I get a similar error message:
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 ' '

As far as I can tell I'm using the correct syntax.

Any ideas?

Cheers
M

AVG returning NULL - temporal values?

auth1 — Sat, 18 May 2013 14:36:37 GMT

I have a MySQL query that works in 5.1 but not 5.5:

SELECT AVG(sales) FROM `table` WHERE `name` = 'customer1' AND `date` > DATE_SUB(NOW(), INTERVAL 1 WEEK)

I had a quick look on the reference manual and it appears to be due to a change of how it handles the date field, specifically:

"The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.) To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value."

I understand that if I were to apply AVG() to the date, for example, I would need to convert the date field. But what do I do in this situation?

Updating the same table for date.

firblazer — Fri, 17 May 2013 04:07:44 GMT

Hi!

I have a media table:

id, endDateTime, expiryDateTime, fkCategoryId
1, 2013-02-01 01:00:15, 2013-5-17 01:00:15, 3
2, 2013-03-04 05:00:15, 2013-5-13 05:00:15, 3
3, 2013-03-11 03:30:15, 2013-5-11 03:30:15, 4

I want to increment the date by a number of weeks, say 4, from the expiryDateTime already in there. Here is what I do:

UPDATE media SET expiryDateTime = DATE_ADD((SELECT expiryDateTime FROM media WHERE fkCategoryId = 3), INTERVAL 4 WEEK) WHERE fkCategoryId = 3

But MYSQL says: You can't specify target table 'media' for update in FROM clause.

I guess I cannot use the same table. What can I do?

I would also like to check for NULL in the expiryDateTime field or 1000-01-01 00:00:00 and update it with endDateTime plus 4 weeks.

What can I do?

Thanks.

Date interval

Miguel61 — Thu, 16 May 2013 14:06:53 GMT

Hi guys, I need your help.

Every two months, the day sixteen I need run this query select to recover the rows of previous months.

If tried this version with current date (2013-05-16) I've the correct output:

PHP Code:


mysql> SELECT

    DATE_ADD(

    LAST_DAY(

        DATE_ADD(

            LAST_DAY(CURDATE()),

            INTERVAL - 180 DAY

        )

    ),

    INTERVAL 1 DAY

) AS first_day,

 LAST_DAY(

    MAKEDATE(YEAR(CURDATE()) , 40)

) AS last_day;

+------------+------------+

| first_day  | last_day   |

+------------+------------+

| 2013-01-01 | 2013-02-28 |

+------------+------------+

1 row in set

Instead if tried this version with date 2013-07-16 next time, I've this incorrect output; I need this:

PHP Code:


+------------+------------+

| first_day  | LAST_DAY   |

+------------+------------+

| 2013-03-01 | 2013-04-30 |

+------------+------------+

But I've this:

PHP Code:


mysql> SELECT

    DATE_ADD(

    LAST_DAY(

        DATE_ADD(

            LAST_DAY('2013-07-16'),

            INTERVAL - 180 DAY

        )

    ),

    INTERVAL 1 DAY

) AS first_day,

 LAST_DAY(

    MAKEDATE(YEAR('2013-07-16') , 40)

) AS LAST_DAY;

+------------+------------+

| first_day  | LAST_DAY   |

+------------+------------+

| 2013-03-01 | 2013-02-28 |

+------------+------------+

1 row in set

I would really appreciate any help, thank you very much.