No output for each

Xtremer360 · May 2, 2011

I'm not getting any errors but I"m not getting the output I'm expecting. When it gets the characterIDList values if there is a value for that field it needs to take that value then explode it and get the ID and characterName of each of the values so say the value inside of characterIDList was 2,3 then for each of those values it would find the characterName and ID of the value and be able to put each of them as a LI inside the UL. I was hoping I could have the charactersQuery separate because I use it for the dropdown.

$newsQuery = "
    SELECT 
        news.categoryID, 
        news.newsTitle,
        news.newsPicture,
        news.content,
        news.characterIDList
    FROM 
        news
        LEFT JOIN categories
            ON news.categoryID = categories.ID
    WHERE 
        news.ID = '" . $newsID . "'";
$newsResult = mysqli_query ( $dbc, $newsQuery ); // Run The Query 
echo $newsQuery;
$row = mysqli_fetch_array ( $newsResult, MYSQL_ASSOC ); 
$characterIDList = $row[ 'chracterIDList' ];

$characterIDList = explode(',',  $characterIDList);

foreach($characterIDList as $value){
    $charactersQuery = "
        SELECT 
            ID, 
            characterName 
        FROM 
            characters
        ORDER BY 
            characters.characterName";
    $charactersResult = mysqli_query ( $dbc, $charactersQuery ); // Run The Query
}

<div class="field required">
            <label for="charactersDrop">Characters Involved</label>
            <ul id="characterList" style="list-style: none; margin-left: 195px;">
            <?php
              mysqli_data_seek( $charactersResult, 0 );
              while($row = mysqli_fetch_array ( $newsResult, MYSQL_ASSOC )) {
                if ($row['characterName'] == NULL  ) { 
                    
                } else {
             ?> 
                    <li characterID="<?php echo $row['ID']; ?>" characterName="<?php echo htmlentities( $row['characterName'] ); ?>"><?php echo htmlentities($row['characterName']); ?> <a href="#">Remove</a></li>   
             <?php }} ?> 
            </ul>
            <select class="dropdown" name="charactersDrop" id="charactersDrop" title="Characters Dropdown" style="margin-left: 195px;">
                <option value="">- Select -</option>
               <?php
                    mysqli_data_seek( $charactersResult, 0 );
                    while ( $row = mysqli_fetch_array ( $charactersResult, MYSQL_ASSOC ) ) { 
                        print "<option value=\"".$row['ID']."\">".$row['characterName']."</option>\r";
                    }
                ?>
            </select>
            <input type="button" value="Add Character" class="" onclick="CharactersInvolved()"/>
        </div>

Xtremer360 · May 2, 2011

Any ideas?

requinix · May 2, 2011

Any reason you haven't normalized the table? Moved those character IDs into a separate table?

Xtremer360 · May 2, 2011

What do you mean that's how I have them stored in my database table.

requinix · May 2, 2011

No, what you're saying is that you have all the IDs stuffed into one field in a table.

categoryID | ... | characterIDList
-----------+-----+----------------
         1 |     | 1,2,3,4,5
         2 |     | 6,7,8,9,10

What you should have is a separate table with two fields: categoryID and characterID. Then a row for each pair.

categoryID | characterID
-----------+------------
         1 |           1
         1 |           2
         1 |           3
...

And I'll get the original question out of the way with a hint:

SELECT whatever you want about the characters WHERE ID IN (1,2,3,4,5)

Xtremer360 · May 2, 2011

That doesn't make any sense with what I"m trying to do.

Okay so this is inside of the edit a news story form. Its supposed to gather the values inside the characterIDList, which are values of the characters that are mentioned in the news story, and be able to get the characterNames of those characters.

The db structure is more like this: ID stands for the autoincrement primary key for each news story post.

Table: news

ID | ... | characterIDList

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

1 | | 1,2,3,4,5

2 | | 6,7,8,9,10

Table: characaters

ID | ... | characterName

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

1 | | CharacterName 1

2 | | CharacterName 2

requinix · May 2, 2011

Okay, so it's ID instead of categoryID. But the point still stands: instead of having 1,2,3,4,5 in one field you should have a separate table with one row per ID. Then your query would look like

SELECT all character stuff FROM character table
JOIN this new associative table ON associative table's character ID = character table's ID
WHERE associative table's news ID = $newsID

Xtremer360 · May 2, 2011

So since that's not the way I want to go anyone else have any idea how I can combine those two queries. Thank you though.

requinix · May 2, 2011

I guess you missed what I said in my post a bit up the page? Use IN.

Xtremer360 · May 3, 2011

Well after looking and researching I decided to go ahead and use that method with putting each character in a new row in a separate table. Thank you for the input.

However I'm not getting the content and newsPicture & statusID row echoed for the values and I'm not sure why.

php/news.php

<?php
error_reporting(E_ALL);
session_start(); // Access the existing session

// Include the database page
require ('../../inc/dbconfig.php');

$newsID = $_GET['id'];

$isAdmin = $_SESSION['isAdmin'];

$newsQuery = "
    SELECT 
        news.categoryID, 
        news.newsTitle,
        news.newsPicture,
        news.content,
        news.statusID, 
        news.ID,
        characters.characterName,
        characters.ID
    FROM 
        news
        LEFT JOIN categories
            ON news.categoryID = categories.ID
        LEFT JOIN characterArticles
            ON news.ID = characterArticles.newsID
            LEFT JOIN characters
                ON characters.ID = characterArticles.characterID
    WHERE 
        news.ID = '" . $newsID . "'";
$newsResult = mysqli_query ( $dbc, $newsQuery ); // Run The Query 
$row = mysqli_fetch_array ( $newsResult, MYSQL_ASSOC ); 
echo $newsQuery;

$charactersQuery = "
        SELECT 
            ID, 
            characterName 
        FROM 
            characters
        WHERE 
            ID NOT IN (SELECT characterID FROM characterArticles)
        ORDER BY 
            characters.characterName";
$charactersResult = mysqli_query ( $dbc, $charactersQuery ); // Run The Query

if ((isset($isAdmin)) && ($isAdmin == "Yes")) {
    $categoryQuery = "
        SELECT 
            ID, 
            categoryName 
        FROM 
            categories";
    $categoryResult = mysqli_query ( $dbc, $categoryQuery ); // Run The Query
} else{ 
    $categoryQuery = "
        SELECT 
            ID, 
            categoryName 
        FROM 
            categories
        WHERE
            privileges = 'All Users' AND statusID = 1";
    $categoryResult = mysqli_query ( $dbc, $categoryQuery ); // Run The Query
}   
?>

form page

<?php require ('php/news.php'); ?>
    
<script type="text/javascript" src="forms/edit/js/news.js"></script>

<!-- Form -->
<form action="#" id="newsForm" >
<fieldset>
	<legend>Edit News</legend>
        <div class="field required">
		<label for="categoryID">Category</label>
            <select class="dropdown" name="categoryID" id="categoryID" title="Category">
                <option value="">- Select -</option>
                <?php
                    while ( $category_row = mysqli_fetch_array ( $categoryResult, MYSQL_ASSOC ) ) {
                            print "<option value=\"".$category_row['ID']."\" ";
        					if($category_row['ID'] == $row['categoryID']) {
        						     print " SELECTED";
        					}
                            print ">".$category_row['categoryName']."</option>\r";
                    }
                ?>
            </select>
		<span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>
	</div>
        <div class="field required">
		<label for="newsTitle">News Title</label>
		<input type="text" class="text" name="newsTitle" id="newsTitle" title="News Title" value="<?php echo $row['newsTitle']; ?>"/>
		<span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>
	</div>
        <div class="field required">
            <label for="charactersDrop">Characters Involved</label>
            <ul id="characterList" style="list-style: none; margin-left: 195px;">
            <?php
              mysqli_data_seek( $newsResult, 0 );
              while($row = mysqli_fetch_array ( $newsResult, MYSQL_ASSOC )) {
                if ($row['characterName'] == NULL  ) { 
                    
                } else {
             ?> 
                    <li characterID="<?php echo $row['ID']; ?>" characterName="<?php echo htmlentities( $row['characterName'] ); ?>"><?php echo htmlentities($row['characterName']); ?> <a href="#">Remove</a></li>   
             <?php }} ?> 
            </ul>
            <select class="dropdown" name="charactersDrop" id="charactersDrop" title="Characters Dropdown" style="margin-left: 195px;">
                <option value="">- Select -</option>
               <?php
                    mysqli_data_seek( $charactersResult, 0 );
                    while ( $row2 = mysqli_fetch_array ( $charactersResult, MYSQL_ASSOC ) ) { 
                        print "<option value=\"".$row2['ID']."\">".$row2['characterName']."</option>\r";
                    }
                ?>
            </select>
            <input type="button" value="Add Character" class="" onclick="CharactersInvolved()"/>
        </div>
        <div class="field required">
            <label for="newsPicture">News Picture</label>
            <input type="text" class="text" name="newsPicture" id="newsPicture" title="News Picture" value="<?php echo $row['newsPicture']; ?>"/>
	</div>
        <div class="field required">
		<label for="statusID">Character Status</label>
            <select class="dropdown" name="statusID" id="statusID" title="Status">
                <option value="">- Select -</option>
                <?php
                    while ( $status_row = mysqli_fetch_array ( $statusResult, MYSQL_ASSOC ) ) {
                            print "<option value=\"".$status_row['ID']."\" ";
        					if($status_row['ID'] == $row['statusID']) {
        						     print " SELECTED";
        					}
                            print ">".$status_row['statusName']."</option>\r";
                    }
                ?>
            </select>
		<span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>
	</div>
        <div class="field required">
		<label for="newsStory">News Story</label>
		<textarea name="newsStory" id="newsStory" title="News Story"><?php echo $row['content']; ?></textarea>
		<span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>
	</div>
        <input type="hidden" name="defaultCharID" id="defaultCharID" value="<?php echo $defaultCharID; ?>" /> 
        <input type="submit" class="submit" name="editNews" id="editNews" title="Edit News" value="Edit News"/>
</fieldset>
</form>
<!-- /Form -->

<!-- Messages -->
<div class="message message-error">
    <h6>Required field missing</h6>
    <p>Please fill in all required fields. </p>
</div>
<div class="message message-success">
    <h6>Operation succesful</h6>
    <p>Arena was added to the database.</p>
</div>
<!-- /Messages -->

requinix · May 3, 2011

Your news.php is getting the first $row of results, then your form page is mostly ignoring it and reading the rest of the rows. So you might be missing out on that first row somewhere.

Then when that while($row=) loop ends, $row will be false, so printing $row[anything] afterwards won't work.

Xtremer360 · May 3, 2011

It prints the values for the first couple of form elements and then stops.

requinix · May 3, 2011

Does a View Source show any messages from PHP?

Xtremer360 · May 3, 2011

None at all.

requinix · May 3, 2011

Then where does it stop printing?

Xtremer360 · May 3, 2011

I was getting duplicate rows when I ran the query inside of phpmyadmin because of the query so I had to separate part of it. I've posted the new code however I'm still having the same issue. I thought I might have needed to reset the pointer with the mysqli_data_seek function but that didn't fix it. It stops echoing the values when it hits the newsPicture element. It doesn't display its value or the values after it.

<?php
error_reporting(E_ALL);
session_start(); // Access the existing session

// Include the database page
require ('../../inc/dbconfig.php');

$newsID = $_GET['id'];

$isAdmin = $_SESSION['isAdmin'];

$newsQuery = "
    SELECT 
        news.categoryID, 
        news.newsTitle,
        news.newsPicture,
        news.content,
        news.statusID, 
        news.ID
    FROM 
        news
        LEFT JOIN categories
            ON news.categoryID = categories.ID
    WHERE 
        news.ID = '" . $newsID . "'";
$newsResult = mysqli_query ( $dbc, $newsQuery ); // Run The Query 
$row = mysqli_fetch_array ( $newsResult, MYSQL_ASSOC ); 
echo $newsQuery;

$newsArticleCharacters = "
    SELECT 
        characters.characterName,
        characters.ID
    FROM
        characterArticles
        LEFT JOIN characters
            ON characterArticles.characterID = characters.ID
    WHERE
        characterArticles.newsID = '" . $newsID . "'";
$newsCharactersResult = mysqli_query ( $dbc, $newsArticleCharacters ); // Run The Query   

$charactersQuery = "
        SELECT 
            ID, 
            characterName 
        FROM 
            characters
        WHERE 
            ID NOT IN (SELECT characterID FROM characterArticles)
        ORDER BY 
            characters.characterName";
$charactersResult = mysqli_query ( $dbc, $charactersQuery ); // Run The Query

if ((isset($isAdmin)) && ($isAdmin == "Yes")) {
    $categoryQuery = "
        SELECT 
            ID, 
            categoryName 
        FROM 
            categories";
    $categoryResult = mysqli_query ( $dbc, $categoryQuery ); // Run The Query
} else{ 
    $categoryQuery = "
        SELECT 
            ID, 
            categoryName 
        FROM 
            categories
        WHERE
            privileges = 'All Users' AND statusID = 1";
    $categoryResult = mysqli_query ( $dbc, $categoryQuery ); // Run The Query
}   
?>

<?php require ('php/news.php'); ?>
    
<script type="text/javascript" src="forms/edit/js/news.js"></script>

<!-- Form -->
<form action="#" id="newsForm" >
<fieldset>
	<legend>Edit News</legend>
        <div class="field required">
		<label for="categoryID">Category</label>
            <select class="dropdown" name="categoryID" id="categoryID" title="Category">
                <option value="">- Select -</option>
                <?php
                    while ( $category_row = mysqli_fetch_array ( $categoryResult, MYSQL_ASSOC ) ) {
                            print "<option value=\"".$category_row['ID']."\" ";
        					if($category_row['ID'] == $row['categoryID']) {
        						     print " SELECTED";
        					}
                            print ">".$category_row['categoryName']."</option>\r";
                    }
                ?>
            </select>
		<span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>
	</div>
        <div class="field required">
		<label for="newsTitle">News Title</label>
		<input type="text" class="text" name="newsTitle" id="newsTitle" title="News Title" value="<?php echo $row['newsTitle']; ?>"/>
		<span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>
	</div>
        <div class="field required">
            <label for="charactersDrop">Characters Involved</label>
            <ul id="characterList" style="list-style: none; margin-left: 195px;">
            <?php
              while($row = mysqli_fetch_array ( $newsCharactersResult, MYSQL_ASSOC )) {
                if ($row['characterName'] == NULL  ) { 
                    
                } else {
             ?> 
                    <li characterID="<?php echo $row['ID']; ?>" characterName="<?php echo htmlentities( $row['characterName'] ); ?>"><?php echo htmlentities($row['characterName']); ?> <a href="#">Remove</a></li>   
             <?php }} ?> 
            </ul>
            <select class="dropdown" name="charactersDrop" id="charactersDrop" title="Characters Dropdown" style="margin-left: 195px;">
                <option value="">- Select -</option>
               <?php
                    mysqli_data_seek( $charactersResult, 0 );
                    while ( $row2 = mysqli_fetch_array ( $charactersResult, MYSQL_ASSOC ) ) { 
                        print "<option value=\"".$row2['ID']."\">".$row2['characterName']."</option>\r";
                    }
                ?>
            </select>
            <input type="button" value="Add Character" class="" onclick="CharactersInvolved()"/>
        </div>
        <div class="field required">
            <label for="newsPicture">News Picture</label>
            <input type="text" class="text" name="newsPicture" id="newsPicture" title="News Picture" value="<?php echo $row['newsPicture']; ?>"/>
	</div>
        <div class="field required">
		<label for="statusID">Character Status</label>
            <select class="dropdown" name="statusID" id="statusID" title="Status">
                <option value="">- Select -</option>
                <?php
                    mysqli_data_seek( $newsResult, 0 );
                    while ( $status_row = mysqli_fetch_array ( $statusResult, MYSQL_ASSOC ) ) {
                            print "<option value=\"".$status_row['ID']."\" ";
        					if($status_row['ID'] == $row['statusID']) {
        						     print " SELECTED";
        					}
                            print ">".$status_row['statusName']."</option>\r";
                    }
                ?>
            </select>
		<span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>
	</div>
        <div class="field required">
		<label for="newsStory">News Story</label>
		<textarea name="newsStory" id="newsStory" title="News Story"><?php echo $row['content']; ?></textarea>
		<span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>
	</div>
        <input type="hidden" name="defaultCharID" id="defaultCharID" value="<?php echo $defaultCharID; ?>" /> 
        <input type="submit" class="submit" name="editNews" id="editNews" title="Edit News" value="Edit News"/>
</fieldset>
</form>
<!-- /Form -->

<!-- Messages -->
<div class="message message-error">
    <h6>Required field missing</h6>
    <p>Please fill in all required fields. </p>
</div>
<div class="message message-success">
    <h6>Operation succesful</h6>
    <p>Arena was added to the database.</p>
</div>
<!-- /Messages -->

requinix · May 3, 2011

while($row = mysqli_fetch_array ( $newsCharactersResult, MYSQL_ASSOC ))

That code is trashing the value of $row. Inside the loop is fine, but outside $row=false and you won't be able to print anything that used to be in it. That includes the newsPicture, statusID (in the next while loop), and content bits you try to use later.

Xtremer360 · May 4, 2011

Thank you!

Sign In

No output for each

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information