Php help manual fetch?

[mikster]

Hi,

I don't know much about php but I have a understanding of programming. My site is written in php and my programmer is MIA so i gotta get this figured out on my own. I'm trying to organize my category list and I was wondering if someone can tell me how to fetch the category out from the mysql.

Atm the code to extract the categories is done in a loop and i don't know the syntax to figure this out.

<? $i=1; while ($crows = bb_db_fetch_array($catsql)) { $n = ($i%3); ?>
<td>
<? if (@in_array($crows['category_id'],$excatg)) { ?>
<input type="checkbox" name="categories[]" value="<?=$crows['category_id']?>" checked>&nbsp;<?=$crows['category_name']?>
<? } else { ?>
<input type="checkbox" name="categories[]" value="<?=$crows['category_id']?>">&nbsp;<?=$crows['category_name']?>
<? } ?>
</td>
<? if ($i>0 && $n==0) { ?>
</tr>
<? } ?>
<? $i++; } ?>


mysql looks like this
---------------------------------
category_id | category_name |
---------------------------------
1 Cars
2 BMW
etc

is it possible to have a code to fetch it by its category_id # ?? If so can someone help me out or point me to the right direction. Thanks alot!!!

[JShor]

Something like this?

$catsql = mysql_query("SELECT * FROM `mytable` WHERE category_id = 9") or die(mysql_error()); 

[mikster]

Hi Jshor

Let me know if i'm getting this right...that code you pasted fetches the data from the mytable category 9 into $catsql?? If so how should I edit the input part??

<input type="checkbox" name="categories[]" value="<?=$crows['category_id']?>">&nbsp;<?=$crows['category_name']?>

Something like this?

$catsql = mysql_query("SELECT * FROM `mytable` WHERE category_id = 9") or die(mysql_error()); 

[traq]

...my programmer is MIA...

Atm the code to extract the categories is done in a loop and i don't know the syntax to figure this out.

<? $i=1; while ($crows = bb_db_fetch_array($catsql)) { $n = ($i%3); ?>
    <td>
<? if (@in_array($crows['category_id'],$excatg)) { ?>
        <input type="checkbox" name="categories[]" value="<?=$crows['category_id']?>" checked>&nbsp;<?=$crows['category_name']?>
<? } else { ?>
        <input type="checkbox" name="categories[]" value="<?=$crows['category_id']?>">&nbsp;<?=$crows['category_name']?>                                       
<? } ?>    
    </td>
<? if ($i>0 && $n==0) { ?>
</tr>
<? } ?>
<? $i++; } ?>

geez, that's messy. the syntax is very outdated and might not even work on some servers (depending on php version and configuration). You should always use full <?php ?> tags; and <?= is bad practice as well. Plus, the php code is mixed with the html markup, making it very hard to track what's supposed to be happening.

I can appreciate your problems with your developer (hire a better one next time).

Let's start with what you actually want to do. This code is not in contex, so I can't guarantee that what I'm doing will work. Likewise, I don't know specifically what your developer's functions do, so I'll be using standard ones.

If you decide to try anything I suggest here, save the code you have now as a backup first.

<?php
// i assume you're making a list of checkboxes,
// based on categories in your db.  so:

// I don't know what the name of your table is
$table = 'tableNameGoesHere';
// fetch all category records
// this assumes you're already connected to your database
$result = mysql_query("SELECT * FROM $table");
// loop through each record and create each from input
while($r = mysql_fetch_assoc($result)){
     // we're going to save each <input> in an array instead of printing them _right_now_
     // this is more flexible, less error-prone, and easier to follow/ work with
     if(in_array($r['category_id'],$excatg)){
          // I don't know what $excatg is, 
          //  but I'm assuming it's an array of options that should be checked by default
          $inputs[] = '
<label><input type="checkbox" name="'.$r['category_id'].'" checked> '.$r['category_name'].'</label>';
     }else{
          $inputs[] = '
<label><input type="checkbox" name="'.$r['category_id'].'"> '.$r['category_name'].'</label>';
     }
}
// print this where-ever it belongs in your html markup
// (I see it inside a <td> in your example)
// also, this will create a space-separated line of checkboxes, 
// which may or may not be what you want and/or nice-looking.  
// Alternatively, as an example,
// you could use '<br>' instead of '&nbsp;' to put each checkbox on its own line.
print implode('&nbsp;',$inputs);
?>

for anything more specific, please share more of your code.

p.s. please use the forum's bbcode tags (e.g., [ php ] /* php code goes here */ [/ php ] (no spaces)) to make your code more readable.

[mikster]

Hi Adrian

First of all thanks for your help. I've tried your code and it listed all the categories in the db in a loop which does the exact same thing as the old code but that's not what I'm looking for. I'm looking for the code so I can display each category name/input manually with a code so i can organize where I want this or that category to be located on the page.

geez, that's messy. the syntax is very outdated and might not even work on some servers (depending on php version and configuration). You should always use full <?php ?> tags; and <?= is bad practice as well. Plus, the php code is mixed with the html markup, making it very hard to track what's supposed to be happening.

I can appreciate your problems with your developer (hire a better one next time).

Let's start with what you actually want to do. This code is not in contex, so I can't guarantee that what I'm doing will work. Likewise, I don't know specifically what your developer's functions do, so I'll be using standard ones.

If you decide to try anything I suggest here, save the code you have now as a backup first.

<?php
// i assume you're making a list of checkboxes,
// based on categories in your db.  so:

// I don't know what the name of your table is
$table = 'tableNameGoesHere';
// fetch all category records
// this assumes you're already connected to your database
$result = mysql_query("SELECT * FROM $table");
// loop through each record and create each from input
while($r = mysql_fetch_assoc($result)){
     // we're going to save each <input> in an array instead of printing them _right_now_
     // this is more flexible, less error-prone, and easier to follow/ work with
     if(in_array($r['category_id'],$excatg)){
          // I don't know what $excatg is, 
          //  but I'm assuming it's an array of options that should be checked by default
          $inputs[] = '
<label><input type="checkbox" name="'.$r['category_id'].'" checked> '.$r['category_name'].'</label>';
     }else{
          $inputs[] = '
<label><input type="checkbox" name="'.$r['category_id'].'"> '.$r['category_name'].'</label>';
     }
}
// print this where-ever it belongs in your html markup
// (I see it inside a <td> in your example)
// also, this will create a space-separated line of checkboxes, 
// which may or may not be what you want and/or nice-looking.  
// Alternatively, as an example,
// you could use '<br>' instead of '&nbsp;' to put each checkbox on its own line.
print implode('&nbsp;',$inputs);
?>

for anything more specific, please share more of your code.

p.s. please use the forum's bbcode tags (e.g., [ php ] /* php code goes here */ [/ php ] (no spaces)) to make your code more readable.

[traq]

do you mean each category name/input, as above, but you want to be able to print each one out individually, wherever you want it?

Because the php code is no longer mixed up with the html output, we can make that change with just a few modifications:

<?php 
$table = 'tableNameGoesHere'; 
$result = mysql_query("SELECT * FROM $table"); 
while($r = mysql_fetch_assoc($result)){ 
     if(in_array($r['category_id'],$excatg)){ 
          // here's the changes: instead of making a 0-indexed array,
          // we'll use the category name as the index key.
          // this will give us something to call on later
          // (you could also use the category id if you wished, 
          //  or even just leave the code as-is 
          //  and print each category in the order they are retrieved from the DB,
          //  like " print $inputs[0]; " or " print $inputs[5]; ")
          $inputs[$r['category_name']] = ' 
<label><input type="checkbox" name="'.$r['category_id'].'" checked> '.$r['category_name'].'</label>'; 
     }else{ 
          $inputs[$r['category_name']] = ' 
<label><input type="checkbox" name="'.$r['category_id'].'"> '.$r['category_name'].'</label>'; 
     } 
} 
// instead of printing _all_ of the inputs, you can choose which one you want
// and print it wherever you like as follows
// (for example, to print the input/category named "BMW"):
print $inputs['BMW'];

// also, you can still print them all out together if you need to, just like before
print implode('<br>',$inputs);
?>

If this is not what you mean, please explain a bit more and post an example of the html output you desire.

[mikster]

Hi Adrian

That's exactly what i want. The basic 101 <input checkbox>category name. I just tried your code and i got an error. It was my bad from the start to not post the header portion of the code maybe.

error
"
Warning: in_array() [function.in-array]: Wrong datatype for second argument in /usr/home
"

header part
"
<?php
session_start();
include("includes/configure.php");
$rpp=24;
if (!isset($_SESSION['member'])) {
echo "<script>location.href='index.php'</script>";
}
$exmod = bb_profile_username_to_profile_details($_SESSION['member']);
$profile = bb_profile_username_to_profile_details($_SESSION['member']);
if (isset($_POST['Submit'])) {
if (isset($_POST['required_albumtitle'])) {
$kategories = implode(",",$_POST['categories']);
$albtitle = str_replace("-", " ", $_POST['required_albumtitle']);
$AddAlbum = bb_db_query("insert into album set album_id='', album_title='".$albtitle."', categories='".$kategories."', username='".$_SESSION['member']."', album_sticker='', album_date='".date('Y-m-d')."', active='".$_POST['required_privacy']."'");
$album_id = bb_db_insert_id();
echo "<script>location.href='UploadPhoto.php?album_id=".$album_id."'</script>";
}

}elseif ($_POST['albumID']>1) {
$kategories = implode(",",$_POST['categories']);
$albtitle = str_replace("-", " ", $_POST['required_albumtitle']);
$UpdateAlbum = bb_db_query("update album set album_title='".$albtitle."', categories='".$kategories."' where album_id='".$_POST['albumID']."'");
echo "<script>location.href='MyAlbum.php?mesg=Album Edited successfully'</script>";

}
if ($_GET['action']=='edit' && $_GET['album']>=1) {
$exalbum = bb_db_fetch_array(bb_db_query("select * from album where username='".$_SESSION['member']."' and album_id='".$_GET['album']."'"));
$excatg = explode(",", $exalbum['categories']);

}

$catsql = bb_db_query("select * from categories order by category_name");

$table = 'categories';
$result = mysql_query("SELECT * FROM $table");
while($r = mysql_fetch_assoc($result)){
if(in_array($r['category_id'],$excatg)){
// here's the changes: instead of making a 0-indexed array,
// we'll use the category name as the index key.
// this will give us something to call on later
// (you could also use the category id if you wished,
// or even just leave the code as-is
// and print each category in the order they are retrieved from the DB,
// like " print $inputs[0]; " or " print $inputs[5]; ")
$inputs[$r['category_name']] = '
<label><input type="checkbox" name="'.$r['category_id'].'" checked> '.$r['category_name'].'</label>';
}else{
$inputs[$r['category_name']] = '
<label><input type="checkbox" name="'.$r['category_id'].'"> '.$r['category_name'].'</label>';
}
}
?>

Let me know if that helps thanks.

[traq]

that's talking about $excatg.

As I said, I don't know what that variable contains and/or how it is created. Since it seemed to be working fine in your original code, I assumed it was an array (which is what in_array() expects as the second argument). If you're using this code somewhere when $excatg is not defined (or is not an array), then you'll have problems.

Since its apparent purpose is to check certain boxes by default, you might try leaving it out (just making all the checkboxes empty by default). However, without knowing everything that is going on in your scripts, I can say for certain if what other problems might crop up as a result.

[mikster]

Hi Adrian thanks for the help I kinda know where u'r getting at. Just one more question isn't there a simple code that I can assign the check box to the corresponding category_id once form is submitted??

Maybe a code like this below but replacing the .$r['category_id'] with .$r['category_id = 5'] instead of putting it the arrays and complex stuff???

<label><input type="checkbox" name="'.$r['category_id'].'" checked> '.$r['category_name'].'</label>';

[traq]

if, say, you want the value to be 5, just write it that way:

$input = '<label><input type="checkbox" name="5">'.$r['category_name'].'</label>'; 

but in doing so, you've lost the association with the original database record.