[SOLVED] selecting the next record from my db

ballhogjoni · February 20, 2007

I can't get the next record from my db when i click the next button. can someonehelp?

<?php

$username="xxxx";
$password="xxxx";
$database="xxxx";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$sql = mysql_query("SELECT * FROM contactinfo ORDER BY id");
$result= mysql_fetch_array($sql);

$sql1 = mysql_query("SELECT * FROM question2checkbox ORDER BY ID");
$result1 =mysql_fetch_array ($sql1);

$sql2 = mysql_query("SELECT * FROM questions123 ORDER BY id");
$result2 =mysql_fetch_array ($sql2);

$sql3 = mysql_query("SELECT * FROM question4 ORDER BY id");
$result3 =mysql_fetch_array ($sql3);

$sql4 = mysql_query("SELECT * FROM question5 ORDER BY id");
$result4 =mysql_fetch_array ($sql4);

$sql5 = mysql_query("SELECT * FROM question6789 ORDER BY id");
$result5 =mysql_fetch_array ($sql5);

mysql_close();
?>

//other code goes here

<p><form action="leads.php" method=post>
<input type=submit value=Next Lead></form>

JBS103 · February 20, 2007

Do you have any code that reacts to the "Next Lead" button being clicked?

Jessica · February 20, 2007

You're selecting all of them, not selecting based on anything.

ballhogjoni · February 20, 2007

thats all my php code if thats what your asking. all exceptmy php code that writes the info into the browser, ie... <?php echo $result5['question7'];?>

ballhogjoni · February 20, 2007

im selecting based off of ID. my objective is to click the next button and then have the code rerun with the next record in my db

Jessica · February 20, 2007

But you're not selecting based off ID, you select *, no WHERE id= $id. You need to add that sort of thing to your queries to get a single record.

ballhogjoni · February 20, 2007

can you write me some code plz?

Jessica · February 20, 2007

No, but I will show you tutorials on what you're trying to do:

http://www.phpfreaks.com/tutorials/142/0.php

http://www.phpfreaks.com/tutorial_cat/25/Page-Number--Pagination.php

ballhogjoni · February 20, 2007

theres gotta be an esaier way than those tutorials. All I am doing is telling the script to get the next record when I clickthe nexy buuton.

Jessica · February 20, 2007

But you're not telling it to do that, so...

You'll need to change your queries to be more like this:

$sql = mysql_query("SELECT * FROM contactinfo WHERE id='$id' ORDER BY id");

and in your form store the $id. Then when you hit next, it processes it by adding one to that, so you can get the next one.

ballhogjoni · February 20, 2007

I must be a completenoob because Im lost. where do you store the variable in the form? in the <form> or <input>

Jessica · February 20, 2007

in a hidden input.

Now, before you ask how to do that just think - if you're not willing to do some research and try to read tutorials and documentation, you're never going to learn anything.

ballhogjoni · February 20, 2007

thx, Imsorry imsuch a pain. :-[ I placed the code: $sql = mysql_query("SELECT * FROM contactinfo WHERE id='$id' ORDER BY id"); in my file and now it doesn't select anything.

tom100 · February 20, 2007

Please read the tutorials posted above by Jesi, they will help a lot. Your where 'id' = $id isn't going to work because

One: Your database probably doesn't even have an id column.

Two: Your selecting different results from different databases. Which is a big sign you need to read those tutorials.

Three: Your form has to submit the current ID then increment it on the next page.

ballhogjoni · February 20, 2007

Please read the tutorials posted above by Jesi, they will help a lot. Your where 'id' = $id isn't going to work because

One: Your database probably doesn't even have an id column.

Two: Your selecting different results from different databases. Which is a big sign you need to read those tutorials.

Three: Your form has to submit the current ID then increment it on the next page.

I have read the tutorials and have implemented them into my script, but its not giving me what I want probably cuz I am a noob with php, so I will continue to read these tutorials and find out what I am missing. I will post more later when I find out what I am doing worng.

One: My db has an ID column in every table.

Two: Just selecting results from different tables in one db

Three: Your right on this one.

ballhogjoni · February 20, 2007

How do I increment this code to retrieve the next record from the db with out going to another page.

<?php
$username="xxxxx";
$password="xxxxx";
$database="xxxxx";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$sql = mysql_query("SELECT * FROM contactinfo ORDER BY id");
$result= mysql_fetch_array($sql);

$sql1 = mysql_query("SELECT * FROM question2checkbox ORDER BY id");
$result1 =mysql_fetch_array ($sql1);

$sql2 = mysql_query("SELECT * FROM questions123 ORDER BY id");
$result2 =mysql_fetch_array ($sql2);

$sql3 = mysql_query("SELECT * FROM question4 ORDER BY id");
$result3 =mysql_fetch_array ($sql3);

$sql4 = mysql_query("SELECT * FROM question5 ORDER BY id");
$result4 =mysql_fetch_array ($sql4);

$sql5 = mysql_query("SELECT * FROM question6789 ORDER BY id");
$result5 =mysql_fetch_array ($sql5);


?>

.josh · February 20, 2007

what exactly are you wanting to increment here? Are those 5 queries examples of what you want to do, as in, you have a list of tables you want to query? Those tables don't appear to be in any kind of pattern that can be incremented. There are methods of finding out all of the tables in your database and then running a loop to query each one, if that's what you want to do. But you're going to have to be more specific in what you are trying to do.

ballhogjoni · February 20, 2007

Thank you for your reply.

I am thinking I want to run a loop of all the queries because I am writing the records I am pulling from the tables to the browser. Of course I didn't post all of my code because after that all it is, is html except for a bunch of echoes like "<?php echo $result['fname'];?>"

basically I would like to click a next button, maybe in a <form> to extract the next record from these tables.

.josh · February 20, 2007

i still don't get it. are those 6 tables the ONLY tables you want to pull info from, and you want to make a loop that will display each row in each table? Or do you have MORE THAN 6 tables and you are wanting to make a loop that loops through each table?

boo_lolly · February 20, 2007

i think he want's the latter.

here's the best you could do, i guess:

<?php
        for($i = 1; $i <= $numQueries; $i++){
                $sql = mysql_query("SELECT * FROM question". $i ." ORDER BY id");
                /*execute query*/
        }
?>

or, alternatively, you could store each query into an array, and use a foreach to execute them in a loop.

ballhogjoni · February 20, 2007

are those 6 tables the ONLY tables you want to pull info from, and you want to make a loop that will display each row in each table? Or do you have MORE THAN 6 tables and you are wanting to make a loop that loops through each table?

Oh crap you stumped me. Umm...I have like 5 to 6 columns in each table that I would like printed to the browser.

Here is a copy of my script (its not that long)

<?php
$username="xxxxx";
$password="xxxxx";
$database="xxxxx";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$sql = mysql_query("SELECT * FROM contactinfo ORDER BY id");
$result= mysql_fetch_array($sql);

$sql1 = mysql_query("SELECT * FROM question2checkbox ORDER BY id");
$result1 =mysql_fetch_array ($sql1);

$sql2 = mysql_query("SELECT * FROM questions123 ORDER BY id");
$result2 =mysql_fetch_array ($sql2);

$sql3 = mysql_query("SELECT * FROM question4 ORDER BY id");
$result3 =mysql_fetch_array ($sql3);

$sql4 = mysql_query("SELECT * FROM question5 ORDER BY id");
$result4 =mysql_fetch_array ($sql4);

$sql5 = mysql_query("SELECT * FROM question6789 ORDER BY id");
$result5 =mysql_fetch_array ($sql5);


?> 
<html>
<head>
<title>Print Leads</title>
</head>
<body bgcolor="#999999">

<table width="800" align="center" border="1" bgcolor="#FFFFFF" cellpadding="5" cellspacing="5">
<tr>
	<td>
		Name: <font color=red><?php echo $result['fname'];?> <?php echo $result['lname'];?></font> | Phone: <font color=red><?php echo $result['areacode']
		;?>-<?php echo $result['prefix'];?>-<?php echo $result['linenumber'];?></font> | Email: 
		<font color=red><?php echo $result['email'
		];?></font> | Position: <font color=red><?php echo $result['position'];?></font> | State: <font color=red><?php echo $result['state'];?></font>
		<p>Are you a surveyor and/or engineer?*
		<br /><font color=red><?php echo $result2['question1'];?></font>
		<p>If you answered ‘Yes’ to the question above, which of the following problems do you face on a daily
		basis?
		<br />Project Tracking: <font color=red><?php echo $result1['question2q1'];?></font>
		<br />Personnel Inefficiency: <font color=red><?php echo $result1['question2q2'];?></font>
		<br />Financial Reporting: <font color=red><?php echo $result1['question2q3'];?></font>
		<br />Invoicing & Billing: <font color=red><?php echo $result1['question2q4'];?></font>
		<br />Scheduling: <font color=red><?php echo $result1['question2q5'];?></font>
		<br />Other:
		<br /><table width="400" height="150" border="1" bordercolor="#000000">
				<tr>
					<td valign="top">
						<font color=red><?php echo $result1['question2q6'];?></font>
					</td>
				</tr>
			</table>
		<p>Would you be interested in a Project Management Program that would result in making your company 15%-30% more efficient in a year’s time--guaranteed?
		<br /><font color=red><?php echo $result2['question2'];?></font>
		<p>If you answered ‘Yes, you are interested' – how interested are you?
		<br /><font color=red><?php echo $result2['question3'];?></font>
		<p>Please rank these in order of importance (1,2,3,4,5) with #1 the most important, #5 the least important, etc.*
		<br />Tracking projects from field to office more efficiently: <font color=red><?php echo $result3['question4q1'];?></font>
		<br />Tracking one and/or multiple employees' times to make your company more efficient: <font color=red><?php echo $result3['question4q2'];?></font>
		<br />Calculate job profitability on every job in real time at a click of a button: <font color=red><?php echo $result3['question4q3'];?></font>
		<br />Give your clients a way to track jobs, order jobs, and check on invoices and statements without ever calling your office: <font color=red><?php echo 
		$result3['question4q4'];?></font>
		<br />Having a Software solution be 100% customizable to your needs: <font color=red><?php echo $result3['question4q5'];?></font>
		<p>Which would you most likely buy in the next 3-6 months.*
		<br />A Total Station: <font color=red><?php echo $result4['question5q1'];?></font>
		<br />Project Management Software: <font color=red><?php echo $result4['question5q2'];?></font>
		<br />A TDS Recon: <font color=red><?php echo $result4['question5q3'];?></font>
		<br />Consulting for your company (teaches your company more efficient surveying & engineering techniques): <font color=red><?php echo $result4['question5q4
		'];?></font>
		<br />Accounting and Payroll Software: <font color=red><?php echo $result4['question5q5'];?></font>
		<br />Other:
		<br /><table width="400" height="150" border="1" bordercolor="#000000">
				<tr>
					<td valign="top">
						<font color=red><?php echo $result4['question5q6'];?></font>
					</td>
				</tr>
			</table>
		<p>Would you be interested in Independent Reviews of various project management software programs designed specifically for surveyors and engineers?*
		<br /><font color=red><?php echo $result5['question6'];?></font>
		<p>If you answered ‘Yes’ to question 6 -- How interested?
		<br /><font color=red><?php echo $result5['question7'];?></font>
		<p>“The first rule of any technology used in a business is that automation applied to an efficient operation will magnify the efficiency." - Bill Gates; 
		Research has shown that implementing a project management program into an efficient firm can save $15,000 to $45,000 a year in company expenses. Would you 
		be interested in a program that can do this? *
		<br /><font color=red><?php echo $result5['question8'];?></font>
		<p>If you answered ‘Yes’ to question 8 -- What would be the best day to contact you and go over a 30 minute demonstration?*
		<br /><font color=red><?php echo $result5['question9'];?></font>
<p>
<form action="" method=post>
<input type=submit name=id value=Next Lead></form>

	</td>
</tr>
</table>
</body>
</html>

trq · February 20, 2007

Explain exactly what it is your trying to do, your code wreaks of poor database design and should be optimized. You might want to do a google for database normalization techniques.

.josh · February 20, 2007

...okay, so you still haven't really answered my question....so... those 6 queries to 6 different tables you have, are because you only have 6 tables, and that you want to get all of the info (all of the columns) from each row from each of those 6 tables. Is that right?

If that is right, then I have to assume that each one of those 6 tables might not have the same amount of rows, so you're going to have to make a seperate loop for each one of them. Each loop would look like this:

<?php
$sql = mysql_query("SELECT * FROM contactinfo ORDER BY id");
// loop through each row in the $sql result source
while ($list = mysql_fetch_array($sql)) {
   // create an array where each element is the info from the fetched row
   $contactinfo[] = $list;
}

$sql1 = mysql_query("SELECT * FROM question2checkbox ORDER BY id");
// loop through each row in the $sql result source
while ($list = mysql_fetch_array($sql)) {
   // create an array where each element is the info from the fetched row
   $question2checkbox[] = $list;
}

// etc... do the same thing for all of the queries
?>

So now you would have 6 different arrays, all named after each of your tables, that will have all the rows in each of the tables. Then in your html where you want to display it, you would loop through each array. example:

<?php
   echo "<table border = '1'>";
   foreach ($contactinfo as $val) {
      echo "<tr>";
      echo    "<td>{$val['column1']}</td>"; 
      echo    "<td>{$val['column2']}</td>";
      echo    "<td>{$val['column3']}</td>";
      echo "</tr>";
   }
   echo "</table>";

   echo "<table border = '1'>";
   foreach ($question2checkbox as $val) {
      echo "<tr>";
      echo    "<td>{$val['column1']}</td>"; 
      echo    "<td>{$val['column2']}</td>";
      echo    "<td>{$val['column3']}</td>";
      echo "</tr>";
   }
   echo "</table>";

   // etc.. etc.. do for each array
?>

you can actually combine those 2 things to remove the need for making an array for each set of data, depending on what kind of layout you're trying to accomplish here. But overall, I agree with thorpe. To the best of my understanding, I have given you an example of what I think you are wanting to do, and it, well, as thorpe said: wreaks of poor db design.

ballhogjoni · February 20, 2007

Sorry Crayon Violent Im such a noob :-[ , but I really do appreciate yor help.

those 6 queries to 6 different tables you have, are because you only have 6 tables, and that you want to get all of the info (all of the columns) from each row from each of those 6 tables. Is that right?

The answer is yes

would I have to intertwine my php and html like you have in ex. 2?

ballhogjoni · February 20, 2007

I have read some stuff on DNT, and from what I have gathered you want to make a new table for different info recorded in the db.

Sign In

[SOLVED] selecting the next record from my db

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

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