PHP MySQL Query If

[jason97673]

Hi there,

 

Basically I have a table of Authors and some of these Authors have there own websites. So I want to do something like If The Author has a website, display the website, if not, don't display anything.

 

Is this possible? I imagine it is.

 

In the database, I will have my table of authors and one field will be website. If there is anything entered in that website field, then display it.

 

Thanks for any help!

[trq]

SELECT website FROM authors WHERE website <> ""

[soycharliente]

<?php
//db connect
$query = "SELECT * FROM authors";
$result = mysql_query($query) or DIE(mysql_error());
if (mysql_num_rows($result) > 0) {
echo "<table>";
echo "<tr><th>Author</th><th>Website</th></tr>";
while ($row = mysql_fetch_array($result)) {
	echo "<tr>";
	$author = $row["author"];
	$website = empty($row["author"]) ? "None" : $row["author"];
	echo "<td>$author</td><td>$website</td>";
	echo "</tr>";
}
echo "</table>";
} else {
echo "No authors at this time.";
}
//db connection close
?>

[redarrow]

<> <WHAT THAT MEAN CHEERS ANYONE

[soycharliente]

Not equal to.

Also the same as !=

[jason97673]

SELECT website FROM authors WHERE website <> ""

 

Thanks for the replies. Only question I have is when you have "" in your mysql_query function in PHP, how do you type that out?

 

For exampe I know

mysql_query("SELECT * from books where website <> """); Isnt going to work so I tried

mysql_query("SELECT * from books where website <> ''");

[per1os]

mysql_query("SELECT * from books where website <> '\"'");

 

escape the double quote.

[trq]

The second one should work fine. You might also check for NULL, depedning on how the data has been added.

 

mysql_query("SELECT website FROM books WHERE website NOT NULL");

[jason97673]

I seem to get an error saying

 

Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\newsite\content\process\showauthor.php on line 30

 

My line 30 is

$info2 = mysql_fetch_array( $data2 );

And Its looking at my data2 variable so on that line I have

$data2 = mysql_query("SELECT website FROM books WHERE website NOT NULL");

 

Perhaps its because I have 3 seperate queries? I dont know if that is good or not but I have

 

$data = mysql_query("SELECT * FROM authors where author_id='$author_id'");
$data1 = mysql_query("SELECT * FROM books where author_id='$author_id'");
$data2 = mysql_query("SELECT website FROM books WHERE website NOT NULL");

$info = mysql_fetch_array( $data );
$info2 = mysql_fetch_array( $data2 );

[Illusion]

that should be

 

SELECT website FROM books WHERE website  IS NOT NULL.

[jason97673]

Ok I changed that but I still get that one error

[soycharliente]

Did you use my code?

[jason97673]

I tried using yours but its a tad different than what I am trying to do. I tried modifying it to fit what I want it to do but failed so I just tried the other SQL statements the above mentioned.

 

Basically you have a list of authors on one page, click there name, go to there author page, where only there data is displayed then on that particular authors page, Id like to have there website show up as a link if there is one in the DB.

 

Ill show you the page I am working on. http://sterlinghousepublisher.com/newsite/content/authors.php?page=31

 

Click on Charles Pero. His page will pop up and under his name I would like his website to be displayed.

 

<?

#Set Root Directory
$root_dir = $_SERVER['DOCUMENT_ROOT'];


#Require that the Global File is included
#This Global File includes the master Include file named "includes.inc.php"
require_once("$root_dir/newsite/includes/global.php");


#Create a Function that will be called down below.
#This Function simply selects the Author_ID and displays the details from the DB

function show_author(){
#Requestthe Author ID from the database
$author_id = $_REQUEST['author_id'];

#Create two Queries. The first Query is to select all data from the Authors table where
#The Author_Id = The Author_ID that was requested above

$data = mysql_query("SELECT * FROM authors where author_id='$author_id'");
#The 2nd Query selects all data from the Books table where the Author_ID = the Author_ID requested above.
$data1 = mysql_query("SELECT * FROM books where author_id='$author_id'");

#Now fetch the data and turn it into an array
$info = mysql_fetch_array( $data );

#Echo some Simple HTML and gather data from the database and display it.
echo '<!--Start of Show Authors Box-->
      <table id="news" class="border" cellpadding="0" cellspacing="0">
      <tr>
 <td style="height:10px"><img src="' .$root_dir.'/newsite/media/books_content.jpg" alt="" /></td>
</tr>
<tr>
 <td style="height:30px; position:relative; top:10px; left:15px" class="text"><b>' .$info['first_name'] .' ' .$info['last_name'] .'</b><br /></td>
</tr>
<tr>
 <td>This is where the Author Website should be listed.
 </td>
</tr>
<tr>
 <td style="height:150px; position:relative; top:20px; left:5px; text-align:left" class="text"><img hspace="10" align="left" src="'.$root_dir.'/newsite/media/images/authors/'.$info['image'] .'.jpg" alt="" />' 
        .$info['description'] .'
</tr>
      <tr>
        <td style="height:50px; position:relative; top:30px; left:15px; width:485px; vertical-align:top" class="text"><b>Books by This Author:</b><br /><br /></td>
      </tr>';
      
#Below we use the 2nd query and turn it into an array
#We then  create a loop to display every book by the Particual Author_id above	
while ($info1=mysql_fetch_array( $data1 ))
{	
echo '<tr>
 <td style="text-align:left"><img align="left" hspace="15" vspace="15" src="' .$root_dir.'/newsite/media/images/books/' .$info1['image'] .'" alt="" /><br /><a href="showbook.php?book_id=' .$info1['book_id'] .'">'.$info1['title'] .'</a></td>
</tr>';
}

echo '</table>';
}

#Combine it all together
#We grab all of our functions and place them together
page_header("Sterlinghouse Publisher: Books that Entertain and Enlightent!");

#Grab all the other functions from my other include files
navbar();
featured_books();
show_author();
featured_podcasts();
picture_of_the_week();
author_interviews();
upcoming_events();
page_footer();


?>

[soycharliente]

So if they have a website, you want their name to be a link, otherwise, just be plain text?

<?php
//db connect
$query = "SELECT * FROM authors";
$result = mysql_query($query) or DIE(mysql_error());
if (mysql_num_rows($result) > 0) {
echo "<table>";
echo "<tr><th>Authors</th></tr>";
while ($row = mysql_fetch_array($result)) {
	echo "<tr>";
	$author = $row["author"];
	$website = $row["website"];
	if (empty($website)) {
		echo "<td>$author</td>";
	} else {
		echo "<td><a href=\"$website\" title=\"$author's website\">$author</a></td>";
	}
	echo "</tr>";
}
echo "</table>";
} else {
echo "No authors at this time.";
}
//db connection close
?>