grumpy

I need some help with a looping problem. In my example code below, I have two Mysql tables: tblDepRatesCats: ID | header | left_text | center_text | right_text | header_order tblRates_balance: id | depratecat | MinBalance | InterestRate | APY | suborder tblDepRatesCats.ID = tblRatesBalance.depratecat. For each row in tblDepRatesCats, there may be 0 or 1 or more rows in tblRates_balance. I'm trying to display the results of querying these tablesso that it shows each tblDepRatesCats data with the corresponding tblRates_balance data, but instead it is showing tblDepRatesCats so that if tblDepRatesCats row has 3 tblDepRatesCats rows associted with it, the tblDepRatesCats row is repeated 3 times with one row of tblDepRatesCats, It displays incorrectly like this: NOW Checking Accounts Minimum Daily Balance to Earn APY $1000 Super NOW Checking Account Minimum Daily Balance to Earn APY $2222 Super NOW Checking Account Minimum Daily Balance to Earn APY $2100 Super NOW Checking Account Minimum Daily Balance to Earn APY $2000 Below is my code. Extra echos included for clarity. I can't figure it out. Any help is appreciated. Thanks. <?php include ('connect.php'); error_reporting(E_ALL); $qry = mysql_query('SELECT DISTINCT tblDepRatesCats.*, tblRates_balance.* FROM tblDepRatesCats INNER JOIN tblRates_balance ON tblDepRatesCats.ID = tblRates_balance.depratecat ORDER BY tblDepRatesCats.header_order, tblRates_balance.suborder;'); $result = $qry or die ("Error:" . mysql_error()); while ($row = mysql_fetch_assoc($result)) { echo ("<table width=\"98%\" border=\"0\" bgcolor:\"#ffffff\"><tr><td>"); echo ("" . $row["header"] . " <br><br>"); echo ("" . $row["left_text"] . " <br><br>"); echo ("</tr></td>"); echo ("<tr><td>"); echo ("" . $row["MinBalance"] . " <br><br>"); echo (" </tr></td>"); } ?>

Thank you for your help. Its working as expected now. I've posted my fixed text code here: <form action="<? echo htmlentities($_SERVER['PHP_SELF']); ?>" name="Edit" method="POST"> <?php $sql = "select * from tblDepRatesCats order by header_order"; $result = $link->query($sql); while($row = mysqli_fetch_assoc($result)){ $ID[] = $row["ID"]; // for testing if(is_array($cattedid)){ echo "IS ARRAY"; }else{ echo"not array"; } echo implode(',',$ID); // end arrray test echo("<hr><input type=\"text\" name=\"ID[]\" value=\"" . $row["ID"] . "\"><p><tr><td>Category name/header text: <input type=\"text\" name=\"header$ID\" value=\"" . $row["header"] . " \" size=\"40\"> Order: <input type=\"text\" name=\"header_order$ID\" value=\"" . $row["header_order"] . "\" size=\"2\"><br> Left sub-text:<input type=\"text\" name=\"left_text$ID\" value=\"" . $row["left_text"] . "\" size=\"25\"> Center sub-text:<input type=\"text\" name=\"center_text$ID\" value=\"" . $row["center_text"] . "\" size=\"25\"> Right sub-text<input type=\"text\" name=\"right_text$ID\" value=\"" . $row["right_text"] . "\" size=\"25\"> <input type=\"checkbox\" name=\"delete_ids[]\" value=\"" . $row["ID"] . "\"> Mark to delete </p> <input type=\"submit\" name=\"editcat\" value=\"EDIT\"> </form> "); //foreach ($ID as $new_depratecat) { //$sql2="SELECT * FROM tblRates_balance where depratecat='$new_depratecat' ORDER BY suborder"; $sql2="SELECT * FROM tblRates_balance where depratecat='{$row['ID']}' ORDER BY suborder"; $result2 = $link->query($sql2); ?> <div style="width:90%;margin:auto;"> <form method="POST" id="new_depratecat"> <div id="itemRows"> Minimum Balance: <input type="text" name="add_MinBalance" size="30" /> Interest Rate: <input type="text" name="add_InterestRate" /> APY: <input type="text" name="add_APY" /> Order: <input type="text" name="add_suborder" size="2"/> << Add data and click on "Save Changes" to insert into db. <br> You can add a new row and make changes to existing rows all at one time and click on "Save Changes." New entry row will appear above after saving. <?php while($rates = mysqli_fetch_array($result2)): ?> <p id="oldRow<?=$rates['id']?>"> Minimum Balance: <input type="text" name="MinBalance<?=$rates['id']?>" value="<?=$rates['MinBalance']?>" /> Interest Rate: <input type="text" name="InterestRate<?=$rates['id']?>" value="<?=$rates['InterestRate']?>" /> APY: <input type="text" name="APY<?=$rates['id']?>" value="<?=$rates['APY']?>" /> Order: <input type="text" name="suborder<?=$rates['id']?>" value="<?=$rates['suborder']?>" /> <input type="checkbox" name="delete_ids[]" value="<?=$rates['id']?>"> Mark to delete</p> <?php endwhile;?> </div> <p><input type="submit" name="ok" value="Save Changes"></p> </form> <?php } //} ?>

Thank you, that helped but not quite solved. . I still have something wrong. Now the result rows show all the rows for its id in the array: http://www.bentleg.com/vieweditratecats7.php Still can't see my problem. any additional help much appreciated.

I've been away from PHP for awhile and need some help with a looping problem. In my example code below, I have two Mysql tables: tblDepRatesCats: ID | header | left_text | center_text | right_text | header_order tblRates_balance: id | depratecat | MinBalance | InterestRate | APY | suborder tblDepRatesCats.ID = tblRatesBalance.depratecat. For each row in tblDepRatesCats, there may be 0 or 1 or more rows in tblRates_balance. When results displayed, it should show each tblDepRatesCats row data followed by the related tblRatesBalance data (and the ability to add/edit more rates). My problem is that I can only get the first row of tblDepRatesCats and its relative tblRates_balance rows to appear. I can't figure out why it won't loop and show the other. You can view my test page here: http://www.bentleg.com/vieweditratecats7.php. My relevant test code is below. Thanks for any assistance. <form action="<? echo htmlentities($_SERVER['PHP_SELF']); ?>" name="Edit" method="POST"> <?php $sql = "select * from tblDepRatesCats order by header_order"; $result = $link->query($sql); while($row = mysqli_fetch_assoc($result)){ $ID[] = $row["ID"]; // for testing if(is_array($cattedid)){ echo "IS ARRAY"; }else{ echo"not array"; } echo implode(',',$ID); // end arrray test echo("<hr><input type=\"text\" name=\"ID[]\" value=\"" . $row["ID"] . "\"><p><tr><td>Category name/header text: <input type=\"text\" name=\"header$ID\" value=\"" . $row["header"] . " \" size=\"40\"> Order: <input type=\"text\" name=\"header_order$ID\" value=\"" . $row["header_order"] . "\" size=\"2\"><br> Left sub-text:<input type=\"text\" name=\"left_text$ID\" value=\"" . $row["left_text"] . "\" size=\"25\"> Center sub-text:<input type=\"text\" name=\"center_text$ID\" value=\"" . $row["center_text"] . "\" size=\"25\"> Right sub-text<input type=\"text\" name=\"right_text$ID\" value=\"" . $row["right_text"] . "\" size=\"25\"> <input type=\"checkbox\" name=\"delete_ids[]\" value=\"" . $row["ID"] . "\"> Mark to delete </p> <input type=\"submit\" name=\"editcat\" value=\"EDIT\"> </form> "); foreach ($ID as $new_depratecat) { $sql2="SELECT * FROM tblRates_balance where depratecat='$new_depratecat' ORDER BY suborder"; $result = $link->query($sql2); ?> <div style="width:90%;margin:auto;"> <form method="POST" id="new_depratecat"> <div id="itemRows"> Minimum Balance: <input type="text" name="add_MinBalance" size="30" /> Interest Rate: <input type="text" name="add_InterestRate" /> APY: <input type="text" name="add_APY" /> Order: <input type="text" name="add_suborder" size="2"/> << Add data and click on "Save Changes" to insert into db. <br> You can add a new row and make changes to existing rows all at one time and click on "Save Changes." New entry row will appear above after saving. <?php while($rates = mysqli_fetch_array($result)): ?> <p id="oldRow<?=$rates['id']?>"> Minimum Balance: <input type="text" name="MinBalance<?=$rates['id']?>" value="<?=$rates['MinBalance']?>" /> Interest Rate: <input type="text" name="InterestRate<?=$rates['id']?>" value="<?=$rates['InterestRate']?>" /> APY: <input type="text" name="APY<?=$rates['id']?>" value="<?=$rates['APY']?>" /> Order: <input type="text" name="suborder<?=$rates['id']?>" value="<?=$rates['suborder']?>" /> <input type="checkbox" name="delete_ids[]" value="<?=$rates['id']?>"> Mark to delete</p> <?php endwhile;?> </div> <p><input type="submit" name="ok" value="Save Changes"></p> </form>

Thanks. I was afraid of that!

I have a file-uploading form to uploads a .jpg file and stores its name in a table in my db as co_bannerfile. I'm trying to now write an update form so the user can update their .jpg file. I've successfully written a query to retrieve the stored file's name. How can I display the stored file's name so the user can see its name within the input TYPE="file" field and then decide to update it or not? The following doesn't display the stored file name. What do I have wrong? Thanks for your help. print "\n<br><INPUT NAME=\"the_file\" TYPE=\"file\" SIZE=\"35\" VALUE=\"" . $co_bannerfile . "\">";

I've worked on my code. Now its showing the drop-down box properly and the user's selections are highligted. But now when it submits, it is not updating the table cat_relations properly. Its putting in only one of the category id's. I'm not seeing any errors and I'm getting the "GOOD" message. Can anyone tell me what I'm still doing wrong? My updates code is below. Thanks. //my form <?php include ('../connect.php'); $ID = '37'; $c = array(); $sql = "SELECT * FROM cat_relations where relation_usr_id = '$ID' "; $result = mysql_query($sql); while ($row = mysql_fetch_array($result)) { $c[$row["relation_cat"]] = " selected " ; } $sql2 = "SELECT * FROM work_cat ORDER BY cat_name ASC"; // execute SQL query and get result $result = mysql_query($sql2); ?> <table width="60%" align="center"> <td> <form name=addcats action=do_updatecats.php?ID=<?php echo $ID; ?> method=POST> <p>Select work categories. Hold down the Ctrl key to make multiple selections. <p> <SELECT name="co_workcat[]" size="4" multiple> <?php while ($row = mysql_fetch_array($result)) { $i = $row["cat_name"]; $strCatId = $row['ID']; echo '<OPTION value="' . $strCatId .' "'. $c[$strCatId]. '>' . $i . '</OPTION>'; } ?> </select> <p><input type=submit name=submit value=SUBMIT> </form> // my processing page - do_updatecats.php <?php include ('../connect.php'); $ID = $_GET['ID']; $cats = $_POST['co_workcat']; foreach($cats as $cat) { $cat_list1 .= $cat . ' '; } ; $query = mysql_query("SELECT ID FROM work_cat where cat_name = '$cat_list1'"); $result = $query; while ($row = mysql_fetch_array($result)) { echo ($row["ID"]); $cat_list1 = $row["ID"]; }; foreach ($cats as $strKey => $strValue) { $sql = "UPDATE cat_relations SET relation_usr_id='$ID',relation_cat='$strValue'"; if (@mysql_query($sql)) { echo ("GOOD"); } else { echo("<p>Error adding cats: " . mysql_error() . "</p>"); } } ?>

Here is my code cobined with your code. <? // populate the drop-down box with the 48 possible selections $sql = "SELECT * FROM work_cat ORDER BY cat_name ASC"; // execute SQL query and get result $result = mysql_query($sql); while ($row = mysql_fetch_array($result)) { $i = $row["cat_name"]; }; $c = array(); $sql = "SELECT * FROM cat_relations where relation_usr_id = '37' "; // execute SQL query and get result $result = mysql_query($sql); while ($row = mysql_fetch_array($result)) { $c[$row["relation_cat"]] = " selected " ; } ?> <table width="60%" align="center"> <td> <form name=addcats action=do_addcats.php?ID=<?php echo $ID; ?> method=POST> <p>Select work categories. Hold down the Ctrl key to make multiple selections. <SELECT name="co_workcat[]" size="4" multiple> <?php while ($row = mysql_fetch_array($result)) { //$i = $row["ID"]; $strCatId = $row['ID']; echo '<OPTION value="' . $strCatId .' "'. $c[$strCatId]. '>' . $i . '</OPTION>'; } ?> </form> </body> </html>

The 48 categories to populate the Select drop-down box should be getting pulled from the table: work_cat by cat_name and ID. I tried the following but it doesn't work: $sql = "SELECT * FROM work_cat ORDER BY cat_name ASC"; // execute SQL query and get result $result = mysql_query($sql); while ($row = mysql_fetch_array($result)) { $i = $row["cat_name"]; }; My test page is here: http://navigator.websitewelcome.com/~freeesti/booking/admin/updatecats-test5.php

Still getting a blank box. I shoud be seeing all 48 categories in the drop-down box with the users 4 previous choices highlighted.

Thanks for your answer. I replaced my code with yours, but I'm just getting an empty SELECT box.

Hi, again- You had helped me yesterday with the initial input. Its a SELECT field, a drop-down box. Their previous selections would show as selected (highlighted) in the drop-down list. They then could change their previous selections if they wanted to and the table would be updated.

Hello- RE: my code below I have an update form where I'm trying to populate a SELECT multiple field with a list of 48 categories, from tbl work_cat. And show, as SELECTED, the one or many choices that the user had previously selected from the 48 categories which are stored in tbl cat_relations as $relation_cat. Then allow the user to update their selections and update the database. But I can't get my form to work. First problem, I can't get the SELECT field on the form to show the categories, and then I don't know where to go from there. Below are my form page and my processing page. Any help would be appreciated. Thanks. //my form <? $sql = "SELECT * FROM cat_relations where relation_usr_id = '37' "; // execute SQL query and get result $result = mysql_query($sql); while ($row = mysql_fetch_array($result)) { $c = $row["relation_cat"] ; } $sql = "SELECT * FROM work_cat ORDER BY cat_name ASC"; $result = mysql_query($sql); // put data into drop-down list box while ($row = mysql_fetch_array($result)) { $i = $row["ID"]; $i = "<OPTION value=\"$i\""; if($c == $i) { $d = "selected"; } else { $d = " "; } $i= "$i</OPTION>"; } ?> <table width="60%" align="center"> <td> <form name=addcats action=do_addcats.php?ID=<?php echo $ID; ?> method=POST> <p>Select work categories. Hold down the Ctrl key to make multiple selections.<br> <SELECT name="co_workcat[]" size="4" multiple> <?php while ($row = mysql_fetch_array($result)) { $i = $row["ID"]; $strCatId = $row['ID']; echo '<OPTION value="' . $strCatId . ' $d">' . $i . '</OPTION>'; } ?> </select> <p><input type=submit name=submit value=SUBMIT> </form> // my processing page <? $cats = $_POST['co_workcat']; foreach($cats as $cat) { $cat_list1 .= $cat . ' '; } ; $query = mysql_query("SELECT ID FROM work_cat where cat_name = '$cat_list1'"); $result = $query; // put data into drop-down list box while ($row = mysql_fetch_array($result)) { echo ($row["ID"]); $cat_list1 = $row["ID"]; }; foreach ($cats as $strKey => $strValue) { $sql = "UPDATE cat_relations SET relation_usr_id='$ID',relation_cat='$strValue'"; if (@mysql_query($sql)) { echo "<script languag='javascript'>location.href='index.php';</script>"; } else { echo("<p>Error adding cats: " . mysql_error() . "</p>"); } }

THANK YOU!!! It works. I really appreciate all your help! I might need your help later when I do a page where the user can access an update page and see the categories stored in the database and then change them

Nows its retrieving the ID's but: 1 its entering the category id's into the relation_usr_id column, one per row, instead of the relation_usr_id 2. its inserting all the category ID's into each row in the relation_cat column, insted of one category id per row