query problem

[slimjim]

Can anyone tell me whay this isn't working. I am not getting any errors, but also not doing anything else either. Havn't done the updating part or deleting part for form, just trying to get banners to show up now.
Should show a form for each banner....
Please help me if you can...

require_once "../conf/common.php";
memberchecklogin();
dbconnect();

if($action=="logout"){
	memberchecklogout();
}

?>

<?
memberheader();
?>
 <table border="0" cellpadding="3" cellspacing="3" width="100%"><tr><td><b>Welcome Back</b> <font size="3" color="#FF0000"><?echo $membername;?><font></td></tr></table>

<table border="0" cellpadding="5" cellspacing="0" width="100%">
<tr>
<td width="100%"><a href="membermain.php">Menu</a> | <a href="membermain.php?action=logout">Logout</a>
  |
</td>
    </tr>
    </table>
<div align="center">
  <center>
  <table border="0" cellpadding="5" cellspacing="0" width="100%">
    <tr>
      <td width="70%" valign="top">
        <table border="0" cellpadding="3" cellspacing="3" width="100%" height="52">
          <tr>
            <td width="100%" bgcolor="<?=getSetting("headbarcolor")?>" height="11"><b><font size="2" color="<?=getSetting("headbartextcolor")?>">Latest
              Account Details</font></b></td>
          </tr>
          <tr>
            <td width="100%" height="21">
<?PHP
//Query to show banners
$query = "SELECT banners.id, banners.name, banners.categoryid, banners.url,
				banners.weighting, banners.filetype, banners.approved, banners.sizeid,
				sizes.width, sizes.height
		FROM banners INNER JOIN sizes ON banners.sizeid = sizes.id
		WHERE banners.memberid = '$loginid'
		ORDER BY banners.name";
//$bannerfound = 0;
       $result = mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");
		while ($myrow = mysql_fetch_array($result)){
	  		$id=$myrow["id"];
	  		$name=$myrow["name"];
	  		$categoryid=$myrow["categoryid"];
	  		$url=$myrow["url"];
	  		$weighting=$myrow["weighting"];
	  	    $filetype=$myrow["filetype"];
	  		$approved=$myrow["approved"];
	  		$sizeid=$myrow["sizeid"];
	  		$width=$myrow["width"];
	  		$height=$myrow["height"];

//Sort out weightings.
		for ($count=1; $count<=5; $count++) {
			if ($count == $weighting) {
				$ws[$count - 1] = " selected";
			} else {
				$ws[$count - 1] = "";
			}
		}
//Check to see if banner was approved
						if($approved =="Y")
						{
							$active="Approved";
						}
						else 
						{
							$active="Awaiting Approval";
						}
?>
            <form method='post' action='banners_manage.php'>
  <table border='0' width='550' cellspacing='0' cellpadding='3' bgcolor='<?=getSetting('areafillcolor')?>' align='center'>
    <tr>
      <td width='30'>
        <input type='hidden' name='id' value='$id'>
      </td>
      <td width='490'>
        <div align='center'><b>$name</b> ($active)</div>
      </td>
      <td width='30'>&nbsp;</td>
    </tr>
    <tr>
      <td width='30'>&nbsp;</td>
      <td width='490'>
        <div align='center'><img src=\"<?=getSetting("siteurl")?>/banners/$id.$filetype\" width=\"$width\" height=\"$height\"></div>
      </td>
      <td width='30'>&nbsp;</td>
    </tr>
    <tr>
      <td width='30' height='89'>&nbsp;</td>
      <td width='490' height='89'> 
          <table border='0' width='100%'>
            <tr> 
              <td width='39%'><font size='2'>URL To Go To:</font></td>

        <td width='61%'><font size='2'>
          <input type='text' name='url' size='30' maxlength='255' value='$url'>
          </font></td>
        </tr>
        <tr> 
          <td width=\"39%\"><font size=\"2\">Category:</font></td>
          <td width=\"61%\">
          <? 

$sql="SELECT id, name FROM categories ORDER by id ASC"; 
$result=mysql_query($sql); 

$options=""; 

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

$cat_id=$row["id"]; 
$cat_name=$row["name"]; 
$options.="<OPTION VALUE=\"$cat_id\">".$cat_name; 
} 
?> 
<SELECT NAME=categoryg> 
<OPTION VALUE=0>Choose 
<?=$options?> 
</SELECT> 
            </td>
            </tr>
            <tr> 
              <td width='39%'><font size='2'>Dimensions:</font></td>
              <td width='61%'>
             <? 

$sql="SELECT id, width, height FROM sizes ORDER by id ASC"; 
$result=mysql_query($sql); 

$options=""; 

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

$pc_id=$row["id"]; 
$pc_width=$row["width"];
$pc_height=$row["height"];
$bothwh = "$pc_width x $pc_height"; 
$options.="<OPTION VALUE=\"$pc_id\">".$bothwh; 
} 
?> 
<SELECT NAME=sized> 
<OPTION VALUE=0>Choose 
<?=$options?> 
</SELECT> 
      </td>
    </tr>
            <tr> 
              <td width='39%'><font size='2'>Weighting:</font></td>
              <td width='61%'>
              <select name='weighting'>
          <option value='1'$ws[0]>1 (Least Shows)</option>
	    <option value='2'$ws[1]>2</option>
	    <option value='3'$ws[2]>3</option>
	    <option value='4'$ws[3]>4</option>
	    <option value='5'$ws[4]>5 (Most Shows)</option>
              </select>
            </td>
            </tr>
            <tr>
              <td width='100%' colspan='2'>
                <div align='center'>
                  <input type='submit' name='update' value='Update Banner'>
                </div>
              </td>
            </tr>
          </table>
      </td>
      <td width='30' height='89'>&nbsp;</td>
    </tr>
    <tr>
      <td width='30'>&nbsp;</td>
      <td width='490'>
        <div align='center'>
          <input type='submit' name='delete' value='Delete Banner'>
        </div>
      </td>
      <td width='30'>&nbsp;</td>
    </tr>
  </table>
  </form>
<?
};
?>
            </td>
          </tr>
        </table>
      </td>
      <td width='30%' valign='top'>
        <div align='center'>
        <table border='0' cellpadding='3' cellspacing='3' width='100%'><tr>      

    <td width='100%' bgcolor='<?=getSetting('headbarcolor')?>'><font size='2' color='<?=getSetting('headbartextcolor')?>'><b>Purchase
      Credits</b></font></td>
    </tr></table>
          <table border='0' cellpadding='0' cellspacing='0' width='100%'>

        <tr>
          <td width='100%'></td>
        </tr>
      </table>
    </div>
  </td>
</tr>
  </table>
  </center>
</div>
<br>

[slimjim]

Ok well i change the one line to this

WHERE banners.memberid = '$membername'

Now i got one form to show, but it should show more then one form..

I also can't get banner to show with this code...

<td width=\"490\">
        <div align=\"center\"><img src=\"<?=getSetting("siteurl")?>/banners/<? echo$id;?><? echo$filetype;?>\" width=\"<? echo$width;?>\" height=\"<? echo$height;?>\"></div>
      </td>

id is banner name and that along with filetype is image name
filetype is .gif

I also echo a few things and got a few other things to show up. but like i said it should show more then one form.... on account of there is more then one banner

[tsinka]

Hi,

you use $result = mysql_query.... more than once. One time outside the outer while loop and inside the while loop. The second $result = ... will overwrite the outer $result so you will get only one of the results of the outer query.

Change $result to $result2 or something like that inside the outer while loop.

Thomas

[slimjim]

ok that part worked.
But still need to get the banners to show on page and how do i make the 2 drop down boxes show the right selcted choice. So if one banner is to be showing category general, then general should be showing, not choose one.....

[tsinka]

Ok,

look at the HTML code where the form starts and the lines below. There are several PHP variable without <?= ?> around them. They will not get interpreted since the HTML code is outside the PHP code blocks.

And you don't need to escape double quotes in HTML code that is outside of the PHP blocks.

About the selects:

Do something like e.g. this:

$sql="SELECT id, name FROM categories ORDER by id ASC";  

$result2=mysql_query($sql);  

$options="";  

while ($row2=mysql_fetch_array($result2)) {  

$cat_id=$row2["id"];  
$cat_name=$row2["name"];  

$selected = ($cat_id == $categoryid) ? " selected" : "";

$options.="<option value=\"$cat_id\"$selected>".$cat_name."</option>\n";  
}  

Do the same with the second select using $sizeid instead of $categoryid.

Thomas

[slimjim]

what about this part :

<select name="weighting">
          <option value="1"$ws[0]>1 (Least Shows)</option>
	    <option value="2"$ws[1]>2</option>
	    <option value="3"$ws[2]>3</option>
	    <option value="4"$ws[3]>4</option>
	    <option value="5"$ws[4]>5 (Most Shows)</option>
              </select>

And i was trying to use this to show what pre exisiting values were but not working....

//Sort out weightings.
		   for ($count=1; $count<=5; $count++) {  

            if ($count == $_POST['weighting']) {  

                $ws[$count - 1] = " selected";  

            } else {  

                $ws[$count - 1] = "";  

            }  

        }

[tsinka]

This part of the HTML is outside the PHP blocks so try something like:

<select name="weighting"> 
  <option value="1"<?=$ws["0"]?>>1 (Least Shows)</option> 
  <option value="2"<?=$ws["1"]?>>2</option> 
  <option value="3"<?=$ws["2"]?>>3</option> 
  <option value="4"<?=$ws["3"]?>>4</option> 
  <option value="5"<?=$ws["4"]?>>5 (Most Shows)</option> 
</select>

You might want to use heredoc syntax if you have much HTML code where you need to use a great number of PHP variables inside that HTML code.

Thomas

[slimjim]

Nope it still isn't showing the selected choice from database

[slimjim]

Yes it did, i forgot i changed the one code and now i went back to the original "for" statement and your idea worked perfect

[tsinka]

Just to test, change the for loop an add a line in front of it:

//Sort out weightings. 
echo "weighting: $weighting<br>\n";

    for ($count=1; $count<=5; $count++) { 
        if ($count == $weighting) { 
            $ws[$count - 1] = " selected"; 
        } else { 
            $ws[$count - 1] = ""; 
        } 
    } 

And change the select code to:

<?
  if (isset($ws) && is_array($ws)) {
    print_r($ws);
  } else {
    "ws array not set!";
  }
?>
<select name="weighting"> 
  <option value="1"<?=$ws["0"]?>>1 (Least Shows)</option> 
  <option value="2"<?=$ws["1"]?>>2</option> 
  <option value="3"<?=$ws["2"]?>>3</option> 
  <option value="4"<?=$ws["3"]?>>4</option> 
  <option value="5"<?=$ws["4"]?>>5 (Most Shows)</option> 
</select> 

Thomas

[tsinka]

Ah,

glad to hear that .... seems like I was too fast with my last post...

Thomas

[slimjim]

Well I am getting close at this.. 🙂
but now cant get this to work and it all looks right to me.

	if(isset($_POST['submitupdate'])){

$act=getSetting("approvedupdate");
						if($act=="Y")
						{
		  $query="UPDATE banners
					SET	url = '$url',
						categoryid = $categoryg,
						weighting = $weighting,
						sizeid = $sized					
					WHERE memberid = '$membername' AND id = '$id'";
						}
						else 
						{
		  $query="UPDATE banners
					SET	url = '$url',
						categoryid = $categoryg,
						weighting = $weighting,
						sizeid = $sized,
						approved = 'N'					
					WHERE memberid = '$membername' AND id = '$id'";
						}
					}

Now i also added a hidden form field that holds the "$id"

[slimjim]

Ok here is the code and i can not get the updateform part to update...

<?

require_once "../conf/common.php";
memberchecklogin();
dbconnect();

if($action=="logout"){
	memberchecklogout();
}
if(isset($_POST['submitdelete'])){

    	$query="DELETE FROM banners
		WHERE memberid = '$membername' AND id = '$id'";

	mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");		



	$res="Successfully deleted an record";

}
	if(isset($_POST['submitupdate'])){
$error="";		
        $bannerid = $_POST['id']; 
		$url = $_POST['url']; 
		$weight = $_POST['weighting']; 
		$category = $_POST['categoryg']; 
		$sizeid = $_POST['sized']; 
		if (!$url)
		{
			$error .= "- You must select a url for the banner.<br>\n";
		}
		if (!$category) 
		{
			$error .= "- You must select a category for the banner.<br>\n";
		}
		if (!$sizeid) 
		{
			$error .= "- You must select the dimensions for this banner.<br>\n";
		}
		if ($weight > 0 && $weight < 6) 
		{
			$error .= "- You must select a weighting for the banner.<br>\n";
		}
$act=getSetting("approvedupdate");
						if($act=="Y")
						{
		  $query="UPDATE banners
					SET	url = '$url',
						categoryid = $category,
						weighting = $weight,
						sizeid = $sizeid					
					WHERE memberid = '$membername' AND id = '$bannerid'";
						}
						else 
						{
		  $query="UPDATE banners
					SET	url = '$url',
						categoryid = $category,
						weighting = $weight,
						sizeid = $sizeid,
						approved = 'N'					
					WHERE memberid = '$membername' AND id = '$bannerid'";
						}
					}
?>
<?PHP
//Query to show banners
$query = "SELECT banners.id, banners.memberid, banners.name, banners.categoryid, banners.url, 
          banners.weighting, banners.filetype, banners.approved, banners.sizeid, 
          sizes.width, sizes.height
		FROM banners INNER JOIN sizes ON banners.sizeid = sizes.id
		WHERE banners.memberid = '$membername'
		ORDER BY banners.name";
//$bannerfound = 0;
       $result = mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");
		while ($myrow = mysql_fetch_array($result)){
	  		$id=$myrow["id"];
	  		$name=$myrow["name"];
	  		$categoryid=$myrow["categoryid"];
	  		$url=$myrow["url"];
	  		$weighting=$myrow["weighting"];
	  	    $filetype=$myrow["filetype"];
	  		$approved=$myrow["approved"];
	  		$sizeid=$myrow["sizeid"];
	  		$width=$myrow["width"];
	  		$height=$myrow["height"];

//Sort out weightings.
/*
		   for ($count=1; $count<=5; $count++) {  

            if ($count == $_POST['weighting']) {  

                $ws[$count - 1] = " selected";  

            } else {  

                $ws[$count - 1] = "";  

            }  

        }
*/
for ($count=1; $count<=5; $count++) { 
            if ($count == $weighting) { 
                $ws[$count - 1] = " selected"; 
            } else { 
                $ws[$count - 1] = ""; 
            } 
        }
//Check to see if banner was approved
						if($approved =="Y")
						{
							$active="Approved";
						}
						else 
						{
							$active="Awaiting Approval";
						}
?>
            <form method="post" action="banners_manage.php">
  <table border="0" width="550" cellspacing="0" cellpadding="3" bgcolor="<?=getSetting("areafillcolor")?>" align="center">
    <tr>
      <td width="30">
        <input type="hidden" name="id" value="$id">
      </td>
      <td width="490">
        <div align="center"><b><? echo$name;?></b> (<? echo$active;?>)</div>
      </td>
      <td width="30">&nbsp;</td>
    </tr>
    <tr>
      <td width="30">&nbsp;</td>
      <td width="490">
<!-- this should show the banner and it isn't showing the dang thing -->
        <div align=\"center\"><img src=\"<?=getSetting("siteurl")?>/banners/<? echo$id;?><? echo$filetype;?>\" width=\"<? echo$width;?>\" height=\"<? echo$height;?>\"></div>
      </td>
      <td width="30">&nbsp;</td>
    </tr>
    <tr>
      <td width="30" height="89">&nbsp;</td>
      <td width="490" height="89"> 
          <table border="0" width="100%">
            <tr> 
              <td width="39%"><font size="2">URL To Go To:</font></td>

        <td width="61%"><font size="2">
          <input type="text" name="url" size="30" maxlength="255" value=<? echo$url;?>>
          </font></td>
        </tr>
        <tr> 
          <td width="39%"><font size="2">Category:</font></td>
          <td width="61%">
          <?
$sql="SELECT id, name FROM categories ORDER by id ASC";   

$result2=mysql_query($sql);   

$options="";   

while ($row2=mysql_fetch_array($result2)) {   

$cat_id=$row2["id"];   
$cat_name=$row2["name"];   

$selected = ($cat_id == $categoryid) ? " selected" : ""; 

$options.="<option value=\"$cat_id\"$selected>".$cat_name."</option>\n";   
}
?>
<SELECT NAME=categoryg> 
<OPTION VALUE=0>Choose 
<?=$options?>  

</SELECT> 
            </td>
            </tr>
            <tr> 
              <td width='39%'><font size='2'>Dimensions:</font></td>
              <td width='61%'>
             <? 

$sql="SELECT id, width, height FROM sizes ORDER by id ASC"; 
$result3=mysql_query($sql); 

$options=""; 

while ($row=mysql_fetch_array($result3)) { 

$pc_id=$row["id"]; 
$pc_width=$row["width"];
$pc_height=$row["height"];
$bothwh = "$pc_width x $pc_height";

$selected = ($pc_id == $sizeid) ? " selected" : ""; 

$options.="<OPTION VALUE=\"$pc_id\"$selected>".$bothwh."</option>\n";
} 
?> 
<SELECT NAME=sized> 
<OPTION VALUE=0>
<?=$options?>
</SELECT> 
      </td>
    </tr>
            <tr> 
              <td width="39%"><font size="2">Weighting:</font></td>
              <td width="61%">
              <select name="weighting"> 
  <option value="1"<?=$ws["0"]?>>1 (Least Shows)</option> 
  <option value="2"<?=$ws["1"]?>>2</option> 
  <option value="3"<?=$ws["2"]?>>3</option> 
  <option value="4"<?=$ws["3"]?>>4</option> 
  <option value="5"<?=$ws["4"]?>>5 (Most Shows)</option> 
</select> 
            </td>
            </tr>
            <tr> 
              <td width="39%"></td>

        <td width="61%"><font size="2">
          <input type="hidden" name="id" size="30" maxlength="255" value=<? echo$id;?>>
          </font></td>
        </tr>
        <tr>
          <td width="100%" colspan="2">
            <div align="center">
              <input type="submit" name="submitupdate" value="Update Banner">
            </div>
          </td>
        </tr>
      </table>
  </td>
  <td width="30" height="89">&nbsp;</td>
</tr>
<tr>
  <td width="30">&nbsp;</td>
  <td width="490">
    <div align="center">
      <input type="submit" name="submitdelete" value="Delete Banner">
    </div>
  </td>
  <td width="30">&nbsp;</td>
</tr>
  </table>
  </form>
<?
};
?>

[tsinka]

Hi,

a) where does $memberid come from in the update queries
b) where is the mysql_query line that executes the update queries ?

Thomas

[slimjim]

memberid is a colum name in table

$act=getSetting("approvedupdate"); 
                        if($act=="Y") 
                        { 
          $query="UPDATE banners 
                    SET    url = '$url', 
                        categoryid = $category, 
                        weighting = $weight, 
                        sizeid = $sizeid                     

                    WHERE memberid = '$membername' AND id = '$bannerid'"; 
                        } 
                        else  

                        { 
          $query="UPDATE banners 
                    SET    url = '$url', 
                        categoryid = $category, 
                        weighting = $weight, 
                        sizeid = $sizeid, 
                        approved = 'N'                     

                    WHERE memberid = '$membername' AND id = '$bannerid'"; 
                        } 
                    }

[slimjim]

Well i even went as far as hard coding it in there and it still doesnt update...

if(isset($_POST['submitupdate'])){
						{
		  $query="UPDATE banners
					SET	url = '$url',
						categoryid = $categoryg,
						weighting = $weighting,
						sizeid = $sized					
					WHERE memberid = 'test' AND id = '5'";

				}

[slimjim]

Well i figured part of it out..
mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");

was missing

[tsinka]

That was what I meant with "b)" ... I should've been clearer about that 🙂

Thomas

[slimjim]

Can you tell me what is wrong with my form checking ?

if(isset($_POST['submitupdate'])){
//checks form
$error="";		
        $bannerid = $_POST['id']; 
		$url = $_POST['url']; 
		$weight = $_POST['weighting']; 
		$category = $_POST['categoryg']; 
		$sizeid = $_POST['sized']; 
		if (!$url)
		{
			$error .= "- You must have a url for the banner.<br>\n";
		}
		if (!$category) 
		{
			$error .= "- You must select a category for the banner.<br>\n";
		}
		if (!$sizeid) 
		{
			$error .= "- You must select the dimensions for this banner.<br>\n";
		}
		if ($weight > 0 && $weight < 6) 
		{
			$error .= "- You must select a weighting for the banner.<br>\n";
		}
$act=getSetting("approvedupdate");
						if($act=="Y")
						{
		  $query="UPDATE banners
					SET	url = '$url',
						categoryid = $category,
						weighting = $weight,
						sizeid = $sized					
					WHERE memberid = '$membername' AND id = '$bannerid'";
					mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");		

					}
					else 
					{
	  $query="UPDATE banners
				SET	url = '$url',
					categoryid = $category,
					weighting = $weight,
					sizeid = $sized,
					approved = 'N'					
				WHERE memberid = '$membername' AND id = '$bannerid'";
                mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");
					}
				}
?>

[tsinka]

You might want to change

if ($weight > 0 && $weight < 6)
to
if ($weight <= 0 || $weight > 6)

Besides that, which error do you get or which part of the code doesn't behave like expected ?
What does the function getSetting do ?

Thomas