[SOLVED] Trouble Calling From Database

[refiking]

I am trying to call the analyst_id from the Analysts table and store it on the Records table.  Here is my code:

 

$analyst = mysql_query(" SELECT `analyst_id` FROM `Analysts` WHERE username = '$username'");

$que = mysql_query($analyst);

while($row = mysql_fetch_assoc($que){

echo $row["analyst_id"]."<br />\n";

}

 

 

What am I doing wrong here?

[Yesideez]

This line has a missing bracket:

while($row = mysql_fetch_assoc($que){

 

It should read:

while($row = mysql_fetch_assoc($que)){

 

And I think this line needs single quotes:

echo $row['analyst_id']."\n";

[refiking]

I am now getting the following error message:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/ctpwebco/public_html/leads/proc.php on line 64

 

 

Line 64 is the following:

 

while($row = mysql_fetch_assoc($que)){

[Yesideez]

Error messages can also refer to the previous line. Check the SELECT query and check it matches with what you have set up for your table.

[refiking]

I looked, but I didn't see anything.  Here are lines 63 - 68:

 

$analyst = mysql_query(" SELECT `analyst_id` FROM `Analysts` WHERE username = '$username'");

$que = mysql_query($analyst);

while($row = mysql_fetch_assoc($que)){

echo $row['analyst_id']."

\n";

}

[Yesideez]

First, is your table called `Analysts` and not `analysts`? Yes, case matters!

 

Second, try changing this line:

$analyst = mysql_query(" SELECT `analyst_id` FROM `Analysts` WHERE `username` = '".$username."'");

 

Also your "echo" statement try keeping the code on one line:

echo $row['analyst_id']."\n";

 

Try that and tell me how it goes.

[refiking]

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/ctpwebco/public_html/leads/proc.php on line 63

 

 

This is line 63:

 

while($row = mysql_fetch_assoc($que)){

[Yesideez]

See this bit?

supplied argument is not a valid MySQL

 

That is telling you that the error is your SELECT query. Remove the space before the "SELECT" bit:

$analyst = mysql_query("SELECT `analyst_id` FROM `Analysts` WHERE `username` = '".$username."'");

 

Try that...

[Yesideez]

My bad - I'm tired as its almost 3am here...

 

$que = mysql_query("SELECT `analyst_id` FROM `Analysts` WHERE username = '$username'");
while($row = mysql_fetch_assoc($que)){
echo $row['analyst_id']."\n";
}

 

I just noticed that you're using mysql_query() twice!!! Use the above code and see if it works.

[refiking]

OK!!!!  That made it echo the id onto the page.  Now, how can I place that in my Records table?

[Yesideez]

So basically you want to pull one record from one table and place it into another table?

[refiking]

Yes.  I think we are saying the same thing.  I want to take the analyst's personal id and place it on each one of their records they are responsible for.  There are two tables.  Analysts and Records.  The Analysts table just stores the info about the analysts.  The Records table stores the info about the records.  Did I make it clear or make it more confusing?

[Yesideez]

That sounds OK to me so I'll give it a shot...

 

Above you've got your code to pull data from the table `Analysts` so now we need to insert that into the table `Records`. Something like this would work:

$que = mysql_query("SELECT `analyst_id` FROM `Analysts` WHERE username = '$username'");
while($row = mysql_fetch_assoc($que)){
  mysql_query("INSERT INTO `Records` VALUES (`analyst_id`) VALUES ('".$row['analyst_id']."')");
}

This presumes you have a field called "analyst_id" defined in your `Records` table.

[refiking]

Yes.  I have the analyst_id field in the Records table.  However, when I ran it, it didn't pllace it in the Records table.

[Yesideez]

Check that analyst_id in both tables are of the same type (ie. SMALLINT or TINYINT)

 

That's all I can think of. You can try adding the "echo" statement back into the loop so you can see what data is being pulled out of the `Analysts` table.

[refiking]

Wait a second!!!  I almost forgot. I am adding the other fields to the same record later in the page (around 250 lines later).  Does that make a difference?

 

The ID is not an INT.  It's a VARCHAR.  When I did the echo, it came right up with no problem.

[Yesideez]

VARCHAR shouldn't matter except that you can't take VARCHAR and insert it into an integer data type if the value is not a number. For example, 12 would go into integer but 12a wouldn't.

 

The later lines shouldn't make a difference because you're still adding records to a table.

[refiking]

Well, I don't know what the problem is then.  I checked the types.  Both are VARCHAR.  When I echo, it shows the id.  Hmmm.....

[Yesideez]

Can yuo give some sample data of analyst_id?

[refiking]

ma123, ma124, and ma125

 

They basically all start with ma.

[Yesideez]

Is the VARCHAR in `Records` big enough?

 

Something like VARCHAR(5) (5 or bigger)

[refiking]

35

[Yesideez]

Add this line into the loop and paste what you get:

echo "INSERT INTO `Records` VALUES (`analyst_id`) VALUES ('".$row['analyst_id']."')".'<br />';

[refiking]

INSERT INTO `Records` VALUES (`analyst_id`) VALUES ('ma1')

[Yesideez]

That looks fine. In your `Records` table, what are all the fields you've got defined? I'm thinking that you need to include some more fields in the INSERT statement.