How to count the number of items returned from resource record (query)

I have returned a bunch of results and put them in a table, which is itself within a form users must fill out. I was going to write a while statement that uses a variable to name each repeating form name unique $name[$i] but need to know how many resources so that I can write the for() statement.  Thanks.
LVL 1
LB1234Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gr8gonzoConsultantCommented:
Usually there is a num_rows function for a resource. For example:
http://php.net/manual/en/mysqli-result.num-rows.php
0
Ray PaseurCommented:
Why not use foreach() ?  Iterators do not need to know how many elements are present.
0
LB1234Author Commented:
i thought you couldn't use a foreach() for a resource record, only a standard PHP array?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

gr8gonzoConsultantCommented:
To add to Ray's point, the while() iterator is typically the best one to use for looping through query results. See the examples here:

http://www.php.net/manual/en/mysqli-result.fetch-assoc.php
0
Ray PaseurCommented:
Want to show us what you've got so far?  We might have some useful ideas.
0
LB1234Author Commented:
Thank you!  one moment!
0
LB1234Author Commented:
Type is a drop down and I'm trying to capture the one the user picks.  I have the $i variable now, and I guess i just need to make a "name = type[$i]" statement in the <option> tag?  I don't know if the code below is correct.  I'm not sure where to put the $i variable.  This is what i have so far.  haven't added the name part yet.

<?php

$get_transactions = "SELECT * FROM transactions order by date ASC LIMIT 10";
$transactions_result_set = mysqli_query($connection, $get_transactions);


$row_cnt = mysqli_num_rows($transactions_result_set);

for ($i=0; $i<=$row_cnt; $i++);

<?php


$type = "<select>";
while ($row = mysqli_fetch_assoc($type_result_set)) {
$type .= sprintf("<option value='%s'>%s</option>", $row["type_name"], $row["type_name"]);
}
$type .= "</select>";
?>

<?php while ($expenses = mysqli_fetch_assoc($transactions_result_set)) { 
?>

 <tr>
 <td><?php echo date("F d, Y", strtotime($expenses["date"]));?></td>
 <td><?php echo number_format($expenses["amount"], 2);?></td>
 <td><?php echo $expenses["vendor"];?></td>
 <td><?php echo $expenses["description"];?></td>
 <td><?php echo $type; ?></td>
 <td><textarea name="notes" id="notes"></textarea></td>
 <td nowrap="nowrap">Trip place holder</td>
 <td><?php echo date("m/d/Y")?></td>
  <td>User Name</td>
<!--  <td><?php// date("F d, Y");?></td>-->
  
 <td><input name="done" type="checkbox" id="done" value="YES"></td>
 
 </tr>
 
 
<?php
}
?>

Open in new window

0
LB1234Author Commented:
I know the sprintf() line needs to fixing so that names are unique, but not sure how.
0
Cornelia YoderArtistCommented:
$get_transactions = "SELECT COUNT(*) as Cnt, * FROM transactions order by date ASC LIMIT 10";
$transactions_result_set = mysqli_query($connection, $get_transactions);
0
Ray PaseurCommented:
It would be interesting to see what that script generated!  This part will let you create the options and the select tag.  The results set is iterated over by the fetch_assoc() functions, and it will come to the end of the results set, then return FALSE.  You might need a data_seek() function to reset the internal pointer if you want to process the results twice.

<?php

// RUN QUERY, TEST FOR ERRORS
$sql = "SELECT * FROM transactions order by date ASC LIMIT 10";
$res = mysqli_query($connection, $get_transactions);
if (!$res) trigger_error('Thud');

// GENERATE THE OPTIONS
$opt = NULL;
while ($row = mysqli_fetch_assoc($res)) 
{
    $opt 
    .= '<option value="'
    . $row["type_name"]
    . '">'
    . $row["type_name"]
    . '</option>'
    . PHP_EOL;
}

// CREATE THE SELECT TAG FROM THE OPTIONS
$select = <<<EOD
<select name="whatever">
$opt
</select>
EOD;

Open in new window

0
LB1234Author Commented:
I don't know what <<< or EOD are.  I'm also confused because all the variable names are different.  I will look them up that syntax also try to understand the code you've written.
0
Ray PaseurCommented:
The <<< notation is called "Heredoc" notation.  It's a very useful way of defining a string when you need variable substitution inside it.  Don't be put off by the warning, just be mindful of the "column one" requirement for the end delimiter.
http://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc
0
LB1234Author Commented:
Would this work Ray?  I know your way is much more elegant than mine, but dealing with refactoring and the concept itself is too much for me, right now.  Very new at this.  Once i get the concept and code working, I'll work on refactoring.

<?php

echo $row_cnt = mysqli_num_rows($transactions_result_set);

for ($i=0; $i<=$row_cnt; $i++);

?>

<td><textarea name="notes<?php echo $i ?>" id="notes"></textarea></td>

Open in new window

0
Ray PaseurCommented:
I don't know if it would work.  I expect it would generate invalid HTML because it appears to be planning for multiple <textarea> tags, but it uses the id= attribute, which must be unique in the document.  You might want to try it out, then use "view source" to see what the PHP script generated.
0
LB1234Author Commented:
the id=attribute is optional anyway, so couldn't i just get rid of it?  The important thing is to make sure that each "notes" text area is captured, i Guess?
0
LB1234Author Commented:
didn't work.  I got rid of the ID field but when i  used

<td><textarea name="notes<?php echo $i ?>"></textarea></td>

view source:

<td><textarea name="notes11"></textarea></td>

so weird... should've counted up sequentially.
0
Ray PaseurCommented:
You could try that.   If you want to style the textarea, you could use a class= attribute.  If you want to use TinyMCE or similar, you might want to set aside some extra test time if there are multiple textarea items on the page.

One of the useful things you can do when working with forms is to add this statement to your action= script.

var_dump($_POST);

That will let you see exactly what the form sent to the action script.
0
LB1234Author Commented:
when i ran this check:

$row_cnt = mysqli_num_rows($transactions_result_set);

for ($i=0; $i<=$row_cnt; $i++) {
	echo "I: $i <br>";	
}

Open in new window


I got:

I: 0
I: 1
I: 2
I: 3
I: 4
I: 5
I: 6
I: 7
I: 8
I: 9
I: 10

which seems to be right... Can't understand why it's not incrementing the number in that name= code.
0
LB1234Author Commented:
DUH!!!!!!!

i know what i did wrong...

i didn't put in an echo in the for statement!!
0
LB1234Author Commented:
Nope that wasn't it.  I think what's happening is the for statement is being completed before the rest of the script runs.  It's not looping through.  I have to put the for statement in the actual <td> line.
0
LB1234Author Commented:
That didn't work either.  When i do this:

<td><textarea name="notes <?php for ($i=0; $i<=$row_cnt; $i++) {
	echo $i;	
} ?>
"></textarea></td>

Open in new window


i get this :  <td><textarea name="notes 012345678910"></textarea></td>
0
LB1234Author Commented:
With the for statement ahead of the table, it's completing and returning a value of "11".  And it's inserting that into name = "notes11".
0
Ray PaseurCommented:
Can you please isolate this part of the issue into the form of the SSCCE and post the code here?  I am fairly sure we can help once we have enough of the code that we can duplicate the issue on our own servers.  Thanks.
0
LB1234Author Commented:
table
Ok I'll try to be as clear as possible.  Every user has a number of charges per month.  My code grabs all of those and puts them into a table.  I'm trying to capture responses to the form in the type, notes, and done areas.  As I understand it, each row of the table must have a unique name so they must be setup as name= type, type1, type 2, so that the captured value isn't overwritten because they all have a common name attribute, as is the case now.  I'm trying to use PHP to insert a "1" after the value for name in the form to make each entry have a unique name.
0
LB1234Author Commented:
nevermind, i got it... i need an array in there to capture the value of $i, and then echo that sequentially, I think.  Let me test this out.
0
Chris StanyonWebDevCommented:
You seem to be taking a 'suck it and see' kind of approach to this, rather than stepping back and thinking about it logically. You look like you're throwing code at your problem in the hope that it will somehow just work. It almost never does! Think about this logically and code accordingly.

The fact that your dropdown options are the same for each expenses row, create the code for this once at the start of your script:

//get the 'options' for the dropdown
$typesQuery = "SELECT type_name from typesTable";
$typesResult = mysqli_query($connection, $typesQuery);

$typeOptions = NULL;

while ($row = mysqli_fetch_assoc($typeResult)) {
	$typeOptions .= sprintf("<option value='%s'>%s</option>".PHP_EOL, $row["type_name"], $row["type_name"]);
}

Open in new window

You now have your dropdown options available to you in a variable called $typeOptions. We'll use it later.

Now you need to create your table rows and form elements based on your query. Here's an overview of that process:

$get_transactions = "SELECT * FROM transactions ORDER BY date ASC LIMIT 10";
$transactions_result_set = mysqli_query($connection, $get_transactions);
while ($expenses = mysqli_fetch_assoc($transactions_result_set)) {
    //code goes in here to create each record
    //you have access to each data record in the $expenses array.
}

Open in new window

At no point does your code need to know how many records are returned nor does it need a consecutive number counter ($i). Each record you pull from your database should have a primary key - a piece of data that is unique to that particular record. If you don't have a unique primary key from your data, then you need to take a step back and create one. Once you've got that, then you can use it when creating your forms, to give each element a unique name. Then within your while{} loop, you would create you form elements like this:

$ID = $expenses['yourPrimaryKey'];
echo "<select name='expense[" . $ID . "][type]'>" . $typeOptions . "</select>";
echo "<textarea name='expense[" . $ID . "][notes]'></textare>";
echo "<input name='expense[" . $ID . "][done]' type='checkbox' value='YES'>";

Open in new window

Now you have unique form inputs that are passed back to your script (assuming you pass the info back to update a database) as an array - one array for each expenses record.

If any of this doesn't make sense, then please ask. You already have a question open that is addressing this issue, so I can only assume that something isn't 'clicking'.
0
LB1234Author Commented:
Chris, thanks so much.  Question, does $ID = $expenses['yourPrimaryKey']; have to go in the
while ($expenses = mysqli_fetch_assoc($transactions_result_set)) {
    //code goes in here to create each record
    //you have access to each data record in the $expenses array.
}

Open in new window


code block?
0
Chris StanyonWebDevCommented:
Yes - you don't have access to anything from your database until you're inside this block - that's what the 'while' line does - it assigns each row of your query to the expenses variable, one row at a time.
0
LB1234Author Commented:
Ok i got everything changed and it's still collecting table data so all appears well.  on the process page, i just wanted to see if the info from the form was captured, but doesn't seem to be working.  Is this syntax correct on the processing page?

<?php


print_r($_POST["expense[][]"]);



?>
0
Ray PaseurCommented:
Start with var_dump($_POST); and refine your data-access strategy after you see the structure of $_POST.
0
LB1234Author Commented:
var dump returns a bunch of information!  This is great!  

 <td><?php echo "<select name='expense[" . $id . "][type]'>" . $type_options . "</select>"?></td>
 
 
 <td><?php echo "<textarea name='expense[" . $id . "][notes]'></textarea>";?></td>

not sure of the syntax of getting specific post information from this though.
0
Ray PaseurCommented:
Hooray!  You have discovered the programmers' best friend: var_dump().  Couple of things.  Do not do this:

<?php echo "<select name='expense[" . $id . "][type]'>" ...

This article explains why that unquoted index is a time-bomb in your code.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12241-Quotation-Marks-in-PHP.html

If you want to post the output of var_dump() here in the code snippet I will be glad to show you how to tease it apart into something useful.
0
LB1234Author Commented:
:)

array (size=1)
  'expense' => 
    array (size=10)
      245 => 
        array (size=3)
          'type' => string 'Asset - Office Equipment' (length=24)
          'notes' => string 'something' (length=9)
          'done' => string 'YES' (length=3)
      246 => 
        array (size=3)
          'type' => string 'Conference: Meeting/Banquet rooms' (length=33)
          'notes' => string 'something' (length=9)
          'done' => string 'YES' (length=3)
      247 => 
        array (size=2)
          'type' => string 'Computer Supplies' (length=17)
          'notes' => string 'something' (length=9)
      248 => 
        array (size=2)
          'type' => string 'Cell Phone' (length=10)
          'notes' => string 'something' (length=9)
      249 => 
        array (size=3)
          'type' => string 'Computer Software' (length=17)
          'notes' => string 'something' (length=9)
          'done' => string 'YES' (length=3)
      250 => 
        array (size=3)
          'type' => string 'Computer Software' (length=17)
          'notes' => string 'something' (length=9)
          'done' => string 'YES' (length=3)
      238 => 
        array (size=2)
          'type' => string 'Conference: Meals - Employee Only' (length=33)
          'notes' => string 'something' (length=9)
      239 => 
        array (size=2)
          'type' => string 'Conference: Meals - Clients/Entertainment' (length=41)
          'notes' => string 'something' (length=9)
      240 => 
        array (size=2)
          'type' => string 'Conference: Miscellaneous' (length=25)
          'notes' => string 'something' (length=9)
      241 => 
        array (size=3)
          'type' => string 'Conference: Meals - Employee Only' (length=33)
          'notes' => string 'something' (length=9)
          'done' => string 'YES' (length=3)

Open in new window

0
Chris StanyonWebDevCommented:
That array is exactly what you want. You can now access the data and loop through each dataset like this:

foreach ($_POST['expense'] as $id => $expenseInfo) {
   //In here you will have access to each row of expense data from your form
   //The primary key (needed for updating your database) is stored in $id
   //The rest of the info is stored in the $expenseInfo array. For example:
   printf("The primary key is %s", $id);
   printf("The 'type' value is %s", $expenseInfo['type']);
   printf("The 'notes' value is %s", $expenseInfo['notes']);
   //Because the 'done' field is a checbox, it will only exist if it was ticked, so you need to use isset()
   if (isset($expenseInfo['done'])) { printf("The 'done' tickbox was ticked"); }
}

Open in new window

0
LB1234Author Commented:
Ok, so I got the array info back properly, and now I need to insert the entries into mysql.  Would that go like this?

<?php


foreach ($_POST['expense'] as $id => $expenseInfo) {
	$query = "INSERT INTO transactions (type, notes, done) VALUES ";
	$query .= "({$expenseInfo["type"]}, {$expenseInfo["notes"]})";

   if (isset($expenseInfo['done'])) { 
      $query2 = "INSERT INTO transactions (done) VALUES"; 
	  $query2 .= "({$expenseInfo["notes"]})";
	  }
}

?>

Open in new window

0
Chris StanyonWebDevCommented:
No. You're running 2 queries there. You would have something like this:

foreach ($_POST['expense'] as $id => $expenseInfo) {
   $type = $expenseInfo["type"];
   $notes = $expenseInfo["notes"];
   $done = (isset($expenseInfo['done']) ? $expenseInfo['done'] : '' ;
   $query = sprintf("INSERT into transactions (type, notes, done) VALUES ('%s', '%s', '%s')", $type, $notes, $done);
   mysqli_query($query);
}

Open in new window

The $done variable is set to the value of the checkbox is it's ticked and an empty string if it's not - change that accordingly.

Also, I would have assumed you would want to UPDATE your database (using your Primary Key), not INSERT new records?
0
LB1234Author Commented:
Argh, you're right -- UPDATE.  What would be the syntax please..  I tried to craft my own, but i know it's wrong :(
0
Chris StanyonWebDevCommented:
Something like this (probably need to change the name of your primary key field):

foreach ($_POST['expense'] as $id => $expenseInfo) {
   $type = $expenseInfo["type"];
   $notes = $expenseInfo["notes"];
   $done = (isset($expenseInfo['done']) ? $expenseInfo['done'] : '' ;
   $query = sprintf("UPDATE transactions SET type = '%s', notes = '%s', done = '%s' WHERE yourPrimaryKey = %s", $type, $notes, $done, $id);
   mysqli_query($query);
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LB1234Author Commented:
Getting Parse error: syntax error, unexpected ';' on line 4 of your code.  Don't see anything wrong it tho!
0
Chris StanyonWebDevCommented:
Doh! Missing closing bracket. Should be:

$done = (isset($expenseInfo['done'])) ? $expenseInfo['done'] : '' ;

Open in new window

0
LB1234Author Commented:
Guys, I finally got this working with all your help.  Chris, I want to especially thank you for dealing with the formidable incompetence of a real noob.  Your help has been incredibly awesome.  Not only can i move forward with the project, but you've really advanced my understanding of coding and the concepts related to this project.  I thank you for the time and effort that went into you re-factoring and fixing my truly terrible code.  I made sure to really try to grasp the code you'd written, rather than just plugging it into the script and moving on.  Still a few things I'm not 100% clear on, but I'll get them.  I guess that's part of the journey and the challenge. ;)

Ray, as always, your continued help and is greatly appreciated.

Lol this was like a bad Oscars award speech.  I'll end it here.  Thanks again guys. :)
0
Chris StanyonWebDevCommented:
No worries. Pleased you got it working, but especially pleased you learned something and understood the code. It's all too easy to copy/paste these days, but learning why and how code works will stand you in good stead.

You know the saying - 'Give a man a fish, he eats for day. Teach a man to fish, he eats for a lifetime' or something like that ;)

As for the incompetence of a real noob and the truly terrible code - we've all been there. Occassionally I come across some old code I've written and I'm amazed that it worked at all...although it can be a worthwhile learning exercise to revisit and refactor it from time to time.

Good luck with your coding
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.