[PHP-DB] select inside a while loop
Hello,
I have two mysql tables songs and artists. They look like this:
CREATE TABLE `artists` (
`artist_id` int(10) unsigned NOT NULL auto_increment,
`artist_name` varchar(100) default NULL,
`artist_img` varchar(50) default NULL,
PRIMARY KEY (`artist_id`),
UNIQUE KEY `artist_name` (`artist_name`),
KEY `artist_id` (`artist_id`)
) TYPE=MyISAM;
CREATE TABLE `songs` (
`song_id` int(11) NOT NULL auto_increment,
`song_title` tinytext,
`artist_id` tinytext,
PRIMARY KEY (`song_id`)
) TYPE=MyISAM;
Currently I have the artist_id in the songs table setup has a text field
with artist names in them temporarily. First I want to select all the
artist_ids(with the names) and find the artist_id for that name in the
artist table. Then update the artist_id in the song table with the
artist_id in the artist table. Then convert the artist_id in the song
table to int.
So with all that said here is what i have done that doesn't work,
$result = mysql_query(SELECT artist_id FROM songs,$db) or
die(mysql_error());
if ($row = mysql_fetch_row($result)){
do {
$artist_name = $row[artist_id];
$result_1 = mysql_query(SELECT artist_id,artist_name FROM
artists WHERE artist_name = '$artist_name',$db);
$row_1 = mysql_fetch_array($result_1);
print $row_1[artist_id]-$row_1[artist_name];
}while ($row = mysql_fetch_array($result));
}
I haven't even been able to get to the update part. I'm pretty sure the
above fails because of the var $artist_name after the first run through.
Any help would be appreciated.
Thanks,
Mike
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql field comparison to multiple variables
Hello, I trying to put together a sql statement that will pull records based on a date field. I can't figure out how to compare the date field to multiple php variables. Something like this, WHERE date_field != $date_1 OR $date_2 OR $date_3 etc. etc. Thanks for the help. Mike -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] md5() and mysql
Hello,
I'm looking at using md5() and mysql for user auth to some of the data
in a table. I found the following on the php md5 manual page,
$query = INSERT INTO user VALUES ('DummyUser',md5('DummyPassword'));
$password = md5($password);
$query = SELECT * FROM user WHERE username='DummyUser' AND
password='DummyPassword';
I see that nobody will be able to view the password once it's in the
database, but I'm thinking that the plain text password is sent to and
from the server and someone might be able to snoop the plain text
password. Is that right?
Thanks for the help,
Mike
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Multiple checkboxes
Hello,
I'm trying to get the values from a form post with multiple checkboxes
checked . Here is what I have,
if ($row = mysql_fetch_array($result));
do {
input type=checkbox name=class_id[] value=$row[class_id] $row[name]
}while ($row = mysql_fetch_array($result));
and when submitted
if ($class_id) {
foreach($class_id as $varName)
print Variable: $varName\n;
}
All I get from this is Invalid argument supplied for foreach(). From
what I could find doing a google search was to create an array using
class_id[] and the above foreach().
Thanks in advance for any help!
Mike
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Insert/Update help needed
Hello all,
I've been able to get the below to work pretty much. The only problem I
have is when there is an apostrophe in the data of one of the fields
like (You're the One). When this occurs nothing gets updated or
inserted(tried both). I've done other things from forms
inserting/updating with data that contained apostrophes and never had a
problem. This is my first attempt at inserting/updating from a string
match.
Any help would be appreciated.
$grab = preg_match_all(/(([\w]+)[^]*)(.*)(\/\\2)/, $file, $matches);
$a_1 = $matches[3][48];
$a_2 = $matches[3][49];
$a_3 = $matches[3][50];
$sql = UPDATE charts SET artist='$a_1' , song='$a_2' , label='$a_3'
WHERE charts_id=1 or die(mysql_error());
if ($result = mysql_query($sql)){
Here is my table structure:
charts_id(tinyint(3),NOT NULL)
artist(varchar(150),NULL)
song(varchar(100),NULL)
label(varchar(75),NULL)
Thanks in advance,
Mike
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Date Ranges
Hi, I'm working on a event calendar and some events span more than one day. I would like to store a date range like 2001-02-05 thru 2001-07-05 so when I do a query for the current day the event would appear for all the days in the range. I hoping someone can point me to some documentation or tutorials on this. Thanks in advance, Mike
[PHP-DB] substring_index Part 2
Thanks for the help on the earlier ?. Now that I have the select statement working I
can't get any results displayed.
$result = mysql_query("SELECT substring_index(body, \".\" ,2) FROM news",$db) or die
("Invalid Query");
if ($myrow = mysql_fetch_array($result)) {
do {
printf("%s\nbr", $myrow["headline"]);
printf("%s\n", $myrow["body"]);
} while ($myrow = mysql_fetch_array($result));
I've tried different mysql functions like mysql_result and mysql_fetch_row, All I get
is a blank page in the browser or a wrong parameter count for mysql_result(). I know
there are records to display i've done the query using the mysql client.
Thanks again for the help,
Mike
[PHP-DB] select substring_index
I have a simple news manager and I would like to display the first two sentences of a
news story. I got this select statement from the mysql mailing list:
SELECT substring_index( body, ". ", 2) FROM news;
This works great from the mysql client but when I try it using php with this:
$result = mysql_query("SELECT substring_index(body, "." ,2) FROM news",$db) or die
("Invalid Query");
if ($myrow = mysql_fetch_array($result)) {
do {
printf("%s\nbr", $myrow["headline"]);
printf("%s\n", $myrow["body"]);
} while ($myrow = mysql_fetch_array($result));
} else {
echo "Sorry, no records were found!";
}
It dies a cold death. I looked at the mysqld.log and the above is not putting the .
in the quotes. I'm stuck in the mud, don't really know if this is even possible. I
would appreciate any pointers.
Thanks in advance,
Mike
[PHP-DB] retrieving images
Hello,
I'm trying to retrieve and output all the binary images in my images table. This is
what I have,
$result = mysql_query("SELECT * FROM images",$db);
if ($myrow = mysql_fetch_array($result)) {
// display list if there are records to display
do {
$filetype = ($myrow["filetype"]);
header("Content-type: $filetype");
echo $myrow["binary_junk"]."br\n";
} while ($myrow = mysql_fetch_array($result));
} else {
// no records to display
echo "Sorry, no records were found!";
When I run this, it only returns the first image in the table. Have really know idea
where to go from here. Is one image all that can be done?
Thanks for the help,
Mike
[PHP-DB] news manager
Hello,
I'm working on a news manager and would like to display records for
today,yesterday,last week, last month and so on. This is what I have so far,
$result = mysql_query("SELECT * FROM news WHERE timestamp=010325",$db);
if ($myrow = mysql_fetch_array($result)) {
Is there a way to do this without having to change the timestamp=value everyday?
Thanks for the Help,
Mike
[PHP-DB] PrintF
Hello, I'm new to PHP and mySQL. I'm trying to find more info on printf. Specifically setting table properties, like height,width,background. Hoping someone can point me in the right direction. Thanks for the Help, Mike
