completely lost ... query statement

[Mamie]

hi,

this is my first post and it is out of desperation. After creating several databases, I'm lost as to how to get them to display on the site. I've downloaded several editors but could not get them to connect to my database, so I'm trying to do it manually. Below is the script I'm trying to use, but I can't get it to work

<?php
$dbhost = 'localhost';
$dbuser = 'admin';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'menus';
mysql_select_db($dbname);

SELECT dessert1; dessert2; dessert3; dessert4;

FROM desserts_of_the_day;

WHERE 'week_day' = 'Monday';
?>

this is the error message that I'm getting:

Parse error: syntax error, unexpected T_STRING . . . . .php on line 10

I know this is probably a very simple code, but I can't get anything to work and after trying for over a week I give up. Any help will be appreciate

thanks,
Mamie

[NogDog]

You have to create a string in PHP with the SQL statement you want to use, then pass it to MySQL via the [man]mysql_query/man function. Assuming it succeeds, you will then have to retrieve the results via [man]mysql_fetch_assoc[man] (or one of the other "fetch" functions). See Example #1 on the [man]mysql_fetch_assoc/man manual page.

[Mamie]

I modified my code:

<?php
$dbhost = 'localhost';
$dbuser = 'admin';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'menus';
mysql_select_db($dbname);

$sql = SELECT dessert1; dessert2; dessert3; dessert4;
FROM desserts_of_the_day;

WHERE 'week_day' = 'Monday';

if (!$result) {
echo "Could not successfully run query ($sql) from DB: " . mysql_error();
exit;
}

if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}

while ($row = mysql_fetch_assoc($result)) {
echo $row["week_day"];
}

mysql_free_result($result);

?>

how my error is:

Parse error: syntax error, unexpected T_STRING . . . on line 11 -- line 11 is in red

is there an editor to easily do this? the one's I downloaded won't connect to the database -- I was instructed to contact my ISP, but comcast said there were no blocks

thanks

[NogDog]

There may be editors that have some "canned" code snippets you can use, but most of us here generally write it from scratch or re-use existing code.

Anyway, you query should be set up as:

$sql = "SELECT dessert1, dessert2, dessert3, dessert4
FROM desserts_of_the_day
WHERE week_day = 'Monday'";

Notice (a) wrapping the entire query in double quotes, (b) use of commas instead of semi-colons within the query (and removing some excess ones), and (c) removing the single quotes around the column name week_day.

Right after that, you then need to submit the query to mysql:

$result = mysql_query($sql);

[Mamie]

thanks, that got rid of that problem - now I'm going to try to fix the new one before I put the results here.

I saved my error plus your solution -- <font size="+2"> with corrections in red so I won't make that mistake again!

like you, I had rather do things by hand -- I still hand code all my html and php includes, but after trying for so long I was frustrated and ready to give up...

[Mamie]

I apologize for my ignorants, but I made all the changes and added the echo script but I'm still getting the error message "No rows found, nothing to print so am exiting"

<?php
$dbhost = 'localhost';
$dbuser = 'admin';
$dbpass = 'password';


$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die  ('Error connecting to mysql');

$dbname = '_menus';
mysql_select_db($dbname);
$sql = "SELECT dessert1, dessert2, dessert3, dessert4 
FROM desserts_of_the_day 
WHERE week_day = 'Monday'"; 

$result = mysql_query($sql); 

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

while ($row = mysql_fetch_assoc($result)) {
    echo $row["week_day.Monday"];
}

mysql_free_result($result);

 while($row = mysql_fetch_assoc($result)){      

  echo "dessert1:".$row['dessert1'].", dessert2:".$row['dessert2']          

  .", dessert3:".$row['dessert3'].", dessert4:".$row['dessert4']."<br/>";
    } 
?> 

thanks

[NogDog]

Are you sure that there are, in fact, any rows in the desserts_of_the_day table that have a value of "Monday" in the week_day column?

[cahva]

Well that means that you have no results with week_day = 'Monday' 🙂

Anyway even if there were any rows, your code would not print out any results because of this:

while ($row = mysql_fetch_assoc($result)) {
echo $row["week_day.Monday"];
}

mysql_free_result($result);

What is this? Tell me why are you printing out $row["week_day.Monday"] which does not even exist. Only fields that you get to the row are the ones that you specify in your query. In this case dessert1,dessert2,dessert3 and dessert4.

After that you're freeing the result and try to use it right after in another loop. I think you were trying to "rewind" the result back to row 0 but that is not necessary.

[Mamie]

this I do know, there are desserts for Monday, other than that I don't have a clue

ID Week_day Dessert1 Dessert2 Desert3 Dessert4

1 Monday BANANA PUDDING BLUEBERRY COBBLER RED VELVET CAKE

2 Tuesday etc,
3 Wednesday etc,

... I thought you specified the column name which is 'week_day' and 'desserts' because desserts, vegetable of the day and specials aren't the same every day --

[NogDog]

Just a hunch that maybe something's going on with the data in the weekday field; try changing the query to:

$sql = "SELECT dessert1, dessert2, dessert3, dessert4
FROM desserts_of_the_day
WHERE week_day LIKE '%Monday%'";

(I'm wondering if there is some white-space characters or something in the field screwing up the comparison.)

[Mamie]

thanks, I tried that query but I'm still getting the error "No rows found, nothing to print so am exiting"

I don't know what "white-space characters" are, but week_day and dessert items are foreign keys in "desserts_of_the_day." Right now I have 43 dessert items - cakes, cobblers, pies, puddings -- and I want to be able to change the dessert menu daily without changing the code each time. Same thing for the vegetable of the day and the special of the day.

thanks

[NogDog]

Mamie;10882531 wrote:

thanks, I tried that query but I'm still getting the error "No rows found, nothing to print so am exiting"

I don't know what "white-space characters" are, but week_day and dessert items are foreign keys in "desserts_of_the_day." Right now I have 43 dessert items - cakes, cobblers, pies, puddings -- and I want to be able to change the dessert menu daily without changing the code each time. Same thing for the vegetable of the day and the special of the day.

thanks

Hmmm....I smell the need for a JOIN or two here. Do you use phpMyAdmin for your database administration? If so, if you open up your database in it and then select the "Export" tab. Make sure "SQL" is selected in the "Export" column on the left, and on the right "Structure" is selected but "Data" is not selected. Then click the "Go" button at the bottom, and copy-and-paste the results here inside of [noparse]

...

[/noparse] tags. Then we can see the exact table structure and - hopefully - figure out what your query really needs to be.

[Mamie]

I appreciate all your help ... I thought you wanted both tables

 Table structure for table `desserts_of_the_day`
--

[COLOR="Red"]CREATE TABLE IF NOT EXISTS `desserts_of_the_day` (
  `ID` int(11) NOT NULL auto_increment,
  `week_day` int(10) default NULL,
  `dessert1` int(10) default NULL,
  `dessert2` int(10) default NULL,
  `dessert3` int(10) default NULL,
  `dessert4` int(10) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;[/COLOR]


Table structure for table `Dessert_items`
--

CREATE TABLE IF NOT EXISTS `Dessert_items` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(150) default NULL,
  `description` text,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=44 ;

Table structure for table `week_day`
--

CREATE TABLE IF NOT EXISTS `week_day` (
  `ID` int(10) NOT NULL auto_increment,
  `_day` varchar(150) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

about 6 years ago I bought AppGini but never used it, so I used it to create my db -- (I've also tried creating tables in phpadmin) -- AppGini is great for setting foreign keys.

[NogDog]

Did you by chance paste the Desert_items definition in both places?

[Mamie]

NogDog;10882534 wrote:

Did you by chance paste the Desert_items definition in both places?

I did ... sorry about that

[NogDog]

OK, so the week_day column is an integer pointing to the primary key of the week_day table, so either you need to change the query to use the applicable week_day.ID value, or else do a JOIN if you only know the weekday name, e.g.:

$sql = "SELECT dessert1, dessert2, dessert3, dessert4
FROM desserts_of_the_day
INNER JOIN week_day ON desserts_of_the_day.week_day = week_day.ID
WHERE week_day._day = 'Monday'";

[Mamie]

I replaced the query with what you had:

<?php
$dbhost = 'localhost';
$dbuser = ' ';
$dbpass = ; ';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'bamabell_menus';
mysql_select_db($dbname);
$sql = "SELECT dessert1, dessert2, dessert3, dessert4
FROM desserts_of_the_day
INNER JOIN week_day ON desserts_of_the_day.week_day = week_day.ID
WHERE week_day._day = 'Monday'";

$result = mysql_query($sql);

if (!$result) {
echo "Could not successfully run query ($sql) from DB: " . mysql_error();
exit;
}

if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}

while ($row = mysql_fetch_assoc($result)) {
echo $row["week_day.Monday"];
}

mysql_free_result($result);

while($row = mysql_fetch_assoc($result)){
echo "dessert1:".$row['dessert1'].", dessert2:".$row['dessert2']

.", dessert3:".$row['dessert3'].", dessert4:".$row['dessert4']."<br/>";
}
?>

now I get this error:

Warning: mysql_fetch_assoc(): 3 is not a valid MySQL result resource in /home/bamabell/public_html/dessert_of_the_day.php on line 33

line 33 is in red ... and I thought doing the database was the hard part!

[NogDog]

The hard part is trying to debug by forum. :p

That mysql_free_result() is blowing away your query result. Try just removing that for now and see what happens.