Transcript SELECT and UPDATE.

Creating Databases
SELECT. UPDATE.
Demonstrate projects.
Classwork / Homework: Prepare to
choose teams & projects.
Warning
• Changes in php from last time.
– THIS IS INEVITABLE and is mainly a good
thing.
• I think I made required changes, but only
did modest testing.
– AND I did all the testing. Always involve other
people to do testing!
SELECT
• … what if you want only DISTINCT
values?
• For example, the trivia quiz starts with a
form to choose the category. People (site
administrators) adding questions can put
in any category.
• http://socialsoftware.purchase.edu/jeanine.
meyer/quiz/choosecategory.php
from php code choosecategory.php
<h1> Welcome to the Quiz </h1> <br>
<h3> Sign in and select a category for your question </h3>
<form action="askquestion.php" method=post>
<p>Name <input type=text name='player' size=30
<?php
…
$query="SELECT DISTINCT category FROM questions";
$categories = mysqli_query($link,$query);
while ($row=mysqli_fetch_array($categories))
{ $cat=$row['category'];
print ("<option value='$cat'>$cat</option><br>\n");
}
SQL join
• SELECT statement against a table made
up by JOINing tables together on identical
fields.
• Different types of JOIN:
– JOIN (same as INNER JOIN)
– LEFT JOIN
– RIGHT JOIN
– FULL JOIN
http://www.w3schools.com/sql/sql_join.asp
LEFT, RIGHT, FULL JOINs
• These provide ways to pick up missing
records.
• CHECK OUT THE w3schools and other
tutorials!
• Will show example from the quiz show
– Task: find questions that player has NOT
answered correctly and has NOT been asked
that day.
Two steps
• Create a temporary table of all the
questions asked a particular player and
answered correctly OR asked today.
– need to specify the contents of the table. In
this case, one field of INT datatype
• If there have been past questions, do a
SELECT using LEFT JOIN to extract any
question NOT present in the past array.
Otherwise, do a simple SELECT
1st step: create temporary table, past
$query="CREATE temporary TABLE past
(item_id INT)";
$query.= " SELECT question_id FROM
history WHERE (player_id='".$player_id;
$query.= "' AND (whenplayed='".$today."'
OR correct))";
$result=mysqli_query($link,$query);
$query="SELECT * FROM past";
$result = mysqli_query($link,$query);
$Num_past = mysqli_num_rows($result);
2nd step
if ($Num_past>0) {
$sel = "SELECT questions.question_id, question,
answerpattern, value from questions";
$sel =$sel . " LEFT JOIN past ON questions.question_id
= past.question_id WHERE ";
$sel = $sel . " category='" . $pickedcategory . "' AND
past.question_id IS NULL";
}
else {
$sel="SELECT question_id, question, answerpattern,
value from questions ";
$sel= $sel . " WHERE category= '" . $pickedcategory.
"'";
}
SELECT Conditions
• WHERE
– Sets condition on individual records
• with a JOIN
– the ON specifying what field to do the JOIN
on, generally a foreign key equal to a primary
key
• After aggregating using GROUP
– HAVING sets a condition on grouped data
Conditions
• Remember: the single equal sign is the
operator for equality!
• Other comparisons: >, <, >=, <=
• LOGIC: AND, OR, NOT
• REGEX for regular expressions
• LIKE: another way to specify a pattern
Conditions
• Can select using set of values
• SELECT * FROM questions WHERE
category IN (‘trivia’,’misc’,’silly’)
• See also BETWEEN
In most cases, people
have only 1 role.
Affleck is an exception.
In most cases, awards
are for 1 role. Producing
is an exception.
Some roles are not
nominated for anything,
hence the 0.
Recall
• 4 tables
movies
people
mid
pid
mname
pname
mdate
…
nominations
aid
roles
rid
rid
0
category
mid
win
pid
…
role (director,actor,etc.)
Tasks
• List all movies by name, ordered by date
– SELECT mname, mdate FROM movies
ORDER BY mdate
• List all people by name with roles in a
given movie, named $moviename (this is
mixture of php and straight SQL)
– SELECT p.pname,m.mname,r.role FROM
movies as m
JOIN roles as r ON m.mid = r.mid
JOIN people as p ON p.pid = r.rid
WHERE m.mname='$moviename'
next task: reuse JOIN clauses
• List all directors (by name), with movie (by
name) ordered by movie name
– SELECT p.pname,m.mname FROM movies
as m
JOIN roles as r ON m.mid = r.mid
JOIN people as p ON p.pid = r.rid
WHERE r.role='Director'
ORDER BY m.mname
• List all movies by name in which someone
was nominated for Best Lead Actor
– SELECT m.mname FROM movies as m
JOIN roles as r ON m.mid = r.mid
JOIN people as p ON p.pid = r.rid
JOIN nominations as n ON n.rid=r.rid
WHERE n.award='Best Lead Actor'
next task, again reuse JOIN
clauses plus start of WHERE
• List all movies by name in which someone was
nominated for an acting category. Count
number.
– Best Lead Actor, Best Lead Actress, Best Supporting
Actor, Best Supporting Actress
– SELECT m.mname, count(*) FROM movies as m
JOIN roles as r ON m.mid = r.mid
JOIN people as p ON p.pid = r.rid
JOIN nominations as n ON n.rid=r.rid
WHERE n.award IN ('Best Lead Actor, Best Lead
Actress, Best Supporting Actor, Best Supporting
Actress) ORDER BY m.mname GROUP BY
m.mname
More
• List movie name, person name, nominated
award ordered by movie name
– SELECT m.mname, p.pname, n.award
FROM movies as m
JOIN roles as r ON m.mid = r.mid
JOIN people as p ON p.pid = r.rid
JOIN nominations as n ON n.rid=r.rid
ORDER BY m.mname
Next
• List winners: movie name, person name,
award
– SELECT m.mname, p.pname, n.award
FROM movies as m
JOIN roles as r ON m.mid = r.mid
JOIN people as p ON p.pid = r.rid
JOIN nominations as n ON n.rid=r.rid
ORDER BY m.mname WHERE n.win='true'
• List movie name, number of people nominated,
ordered from high to low
– SELECT m.mname, count(*) FROM movies as m
JOIN roles as r ON m.mid = r.mid
JOIN people as p ON p.pid = r.rid
JOIN nominations as n ON n.rid=r.rid ORDER BY
m.mname GROUP BY m.mname
– EXTRA CREDIT: figure out how not to count multiples
for awards that have multiples (such as producer,
technical awards)
Research
• Some questions require consideration of
absent records
– What movies were nominated (had people
nominated) for Best Movie but not Best
Director
–?
• Look up and study examples of LEFT
JOINS
UPDATE
• Can update a single record or a set of
records.
• UPDATE questions SET text = ‘$ntext’
WHERE ques_id=‘$qid’
– Assumes table names questions with fields
text and ques_id and php variables $ntext and
$qid set previously
UPDATE
• Raise all the prices 10%
– UPDATE products SET price=1.10*price
• Raise the prices that are over 100 by 5%
– UPDATE products SET price=1.05*price
WHERE price > 100
Projects
• Recall Geolocation / Google maps email.
http://socialsoftware.purchase.edu/jeanine.
meyer/emailing/geolocationkmemail.html
• Quizhttp://faculty.purchase.edu/jeanine.me
yer/html5/mapmediaquiz.html
• Does not use database. Enhancement is
to add database for locations
Projects
• Bookmarks, with password system for
finders
– http://socialsoftware.purchase.edu/jeanine.me
yer/research/addsite.html
– http://socialsoftware.purchase.edu/jeanine.me
yer/research/showsitesbycategory1.php
Projects
• Student departments
http://socialsoftware.purchase.edu/jeanine.
meyer/studentexample/showstudents.php
http://socialsoftware.purchase.edu/jeanine.
meyer/studentexample/showdepartments.
php
http://socialsoftware.purchase.edu/jeanine.
meyer/studentexample/enterstudent.html
Projects
• Trivia quiz
http://socialsoftware.purchase.edu/jeanine.
meyer/quiz/choosecategory.php
http://socialsoftware.purchase.edu/jeanine.
meyer/quiz/inputquestions.php
– edit feature?
– change scoring?
– improve interface?
Projects
• Origami store
http://socialsoftware.purchase.edu/jeanine.
meyer/orders/orderproduct.php
http://socialsoftware.purchase.edu/jeanine.
meyer/orders/inputproducts.php
need file upload to upload pictures
– general improvement
– scale up
Projects
• Songs and features in songs. Find similar
songs
http://socialsoftware.purchase.edu/jeanine.
meyer/example/findothers1.php
http://socialsoftware.purchase.edu/jeanine.
meyer/example/addsong.html
http://socialsoftware.purchase.edu/jeanine.
meyer/example/addfeature.html
http://socialsoftware.purchase.edu/jeanine.
meyer/example/addfeaturestosong1.php
Stories
• http://socialsoftware.purchase.edu/jeanine.
meyer/stories/tellStory.php
• http://socialsoftware.purchase.edu/jeanine.
meyer/stories/enterscene.html
• http://socialsoftware.purchase.edu/jeanine.
meyer/stories/addscene.php
• http://socialsoftware.purchase.edu/jeanine.
meyer/stories/showAllScenesforediting.ph
p
Citations
• http://socialsoftware.purchase.edu/jeanine.
meyer/citations/drawlinksAsArrows.php
• http://socialsoftware.purchase.edu/jeanine.
meyer/citations/inputjournals.php
• http://socialsoftware.purchase.edu/jeanine.
meyer/citations/inputarticles.php
• http://socialsoftware.purchase.edu/jeanine.
meyer/citations/addlink1.php
Assignment
• Team project to present and
• Enhance one of these projects
• There will be an assignment to build
database project totally on your own
Projects
•
•
•
•
•
•
student database
trivia quiz
book marks
songs
origami store
Stories
• Google maps media portal (or quiz)
– Note: this does NOT have a database at all! So
obvious enhancement is to make use of database,
possibly with table for player or ???
• Late addition: citations (needs updating)
Preview (after midterm)
• Self assign or I assign teams (4-5)
• Teams will pick projects
– resolve conflicts
Homework
• Prepare to form teams AND choose
application.
• Working with existing code is…always a
challenge but more common than starting
from nothing.